I would like cell O6 calculate automatically based on the three criteria as written below in the box in blue.

So if FTE percentage (cell M9) is =>50%, then value in column O6 should change to 100%

If FTE percentage (cell M9) is >25% or <50%, then column O6 should change to 50%

If FTE percentage (cell M9) =<25%, then column O6 should change to 25%

Currently I have drop-down box and change it manually, but would like to automate this to avoid error. So that dropdown option box cell can be replaced.

If there's any other smart way to calculate the costs in B32, B37, B42, etc (yellow highlighted cell), then that would be great.

Thanks a lot for your help.

Rae

So if FTE percentage (cell M9) is =>50%, then value in column O6 should change to 100%

If FTE percentage (cell M9) is >25% or <50%, then column O6 should change to 50%

If FTE percentage (cell M9) =<25%, then column O6 should change to 25%

Currently I have drop-down box and change it manually, but would like to automate this to avoid error. So that dropdown option box cell can be replaced.

If there's any other smart way to calculate the costs in B32, B37, B42, etc (yellow highlighted cell), then that would be great.

Thanks a lot for your help.

Rae

Hello,

Attached you will find my spreadsheet with two sheets. I am trying to write a vlookup B14 on Sheet 2 to populate B14-G14, pulling from the first sheet. Is there any way to return multiple columns from a different sheet using vlookup? I am basing my lookup on column a from both sheets. Please help!!

Attached you will find my spreadsheet with two sheets. I am trying to write a vlookup B14 on Sheet 2 to populate B14-G14, pulling from the first sheet. Is there any way to return multiple columns from a different sheet using vlookup? I am basing my lookup on column a from both sheets. Please help!!

Hi,

I am a new excel user and trying to create a cash flow spreadsheet for a rental commercial property with three tenants. I would like to be able to enter the date and have the rents populate in the appropriate month, but also have the entered annual increase go up every 13th month. I started to try to create it on the attached sheet and I seem to have figure out how to grow the rent annually, but I cannot figure out how to also automate the populating by date.

Any help is much appreciated.

Cheers.

Chris

I am a new excel user and trying to create a cash flow spreadsheet for a rental commercial property with three tenants. I would like to be able to enter the date and have the rents populate in the appropriate month, but also have the entered annual increase go up every 13th month. I started to try to create it on the attached sheet and I seem to have figure out how to grow the rent annually, but I cannot figure out how to also automate the populating by date.

Any help is much appreciated.

Cheers.

Chris

I've reviewed the forum and ran across the solution posted back a few years ago in the forum under 866645-average-weighted-grades-with-blank-cells.html#post3081477. It was right up my alley but its not quite doing what I want. ((apologies, I'm new and I can't post the full url to the post I'm referencing))

I am trying to calculate an overall score for a scorecard of four sections. Each section is worth various points and I want to emphasize one or two areas over the others, so I've weighted them to have a bit more of an impact. When applying the formula which was givein in the example above, it appears the average I'm getting in D39 is still taking the black cell (D38) in the attached spreadsheet into account. There will be times where some sections might not be graded and, as such, they should not have an affect on the overall score. If I only score two sections, I want the final score to represent only those two sections (to include the weight).

Using =SUMPRODUCT(D35:D38,C35:C38)/SUMIF(D35:D38,"<>",C35:C38) in the uploaded spreadsheet, three 100%, should score 100%, not 83.33%. Or at least, that's what I'm asking to achieve. I'm sure it's an easy tweak that I'm just overlooking but at this point my brain is done.

Thank you in advance!

I am trying to calculate an overall score for a scorecard of four sections. Each section is worth various points and I want to emphasize one or two areas over the others, so I've weighted them to have a bit more of an impact. When applying the formula which was givein in the example above, it appears the average I'm getting in D39 is still taking the black cell (D38) in the attached spreadsheet into account. There will be times where some sections might not be graded and, as such, they should not have an affect on the overall score. If I only score two sections, I want the final score to represent only those two sections (to include the weight).

Using =SUMPRODUCT(D35:D38,C35:C38)/SUMIF(D35:D38,"<>",C35:C38) in the uploaded spreadsheet, three 100%, should score 100%, not 83.33%. Or at least, that's what I'm asking to achieve. I'm sure it's an easy tweak that I'm just overlooking but at this point my brain is done.

Thank you in advance!

Hi,

I need some help please :)

I have several rows where a specific Event ID (Column A) occurs multiple times. What I want to do is have all the key information on just 1 row without having the Event ID being displayed on multiple rows.

As shown in the below image and attached spreadsheet, in column P, I have for removed the duplicates for each of the IDs and in cell Q2 I have used the formula**=VLOOKUP($P2,$A$2:$L$38,COLUMN(B1),FALSE)** and dragged across + down to populate/create an additional table which I will be working on.

Capture7.PNG

This additional table appears to partially return what I am looking for. However, I need a formula to return the following on a single row for a specific Event ID:

1) Based from the Date column (Column C) I want to return the**first** date shown of a specific ID within the additional table created on the right. For example Event ID 211855 has dates, 01/10/2018 and 02/10/2018 across multiple rows. I just want 01/10/2018 to be returned as it's the earliest date.

2) From the IR Code column (Column L) I want to return the IR code for a specific Event ID in terms of "priority" being shown in the following order: LOM, DAM2, DAM1, AOG, 41, 43. So for example, if in any of the rows for a specific Event ID, "LOM" does not appear, then it looks to return "DAM2", if that does not appear in any of the rows for a specific Event ID and DAM1 does, then "DAM1" is returned, as it's the highest priority code being shown. (Tried to explain this as simple as possible! :))

3) From the additional table I have created, the formula I have used returns all the columns, however, I only want to return specific columns. I want to return columns: B, C, D, G, H, I, J, K, L and M. I have tried just deleting the columns from the additional table created but then it ruins the whole table and returns incorrect values.

I would appreciate it if anyone could help me out please :)

Thanks

I need some help please :)

I have several rows where a specific Event ID (Column A) occurs multiple times. What I want to do is have all the key information on just 1 row without having the Event ID being displayed on multiple rows.

As shown in the below image and attached spreadsheet, in column P, I have for removed the duplicates for each of the IDs and in cell Q2 I have used the formula

Capture7.PNG

This additional table appears to partially return what I am looking for. However, I need a formula to return the following on a single row for a specific Event ID:

1) Based from the Date column (Column C) I want to return the

2) From the IR Code column (Column L) I want to return the IR code for a specific Event ID in terms of "priority" being shown in the following order: LOM, DAM2, DAM1, AOG, 41, 43. So for example, if in any of the rows for a specific Event ID, "LOM" does not appear, then it looks to return "DAM2", if that does not appear in any of the rows for a specific Event ID and DAM1 does, then "DAM1" is returned, as it's the highest priority code being shown. (Tried to explain this as simple as possible! :))

3) From the additional table I have created, the formula I have used returns all the columns, however, I only want to return specific columns. I want to return columns: B, C, D, G, H, I, J, K, L and M. I have tried just deleting the columns from the additional table created but then it ruins the whole table and returns incorrect values.

I would appreciate it if anyone could help me out please :)

Thanks

I'm trying to create a unique list of values based on multiple criteria. Here is my best effort, but not good enough. I tried this in O2:

D is my target list to extract unique values

A matches K2

B matched N2

C matches N3

See attached workbook.

Suggestions?

Sick

HTML Code:

`=IFERROR(LOOKUP(2,1/(COUNTIF($O$1:O1,$D$2:$D$40000)=0)/($A$2:$A$40000=$K$2*$B$2:$B$40000=$N$2*$C$2:$C$40000=$N$3),$D$2:$D$40000),"")`

A matches K2

B matched N2

C matches N3

See attached workbook.

Suggestions?

Sick

Hi All,

I have a data-set that has weekly historical running total data. I have to do an analysis that ties in said weekly data with other data that was recorded monthly. In order to be able to do this I would have to pull data for the last week of each month, however, I am having trouble creating a formula that would do that.

I’ve attached a rudimentary example of the data set to this post.

The data-set has a Location, Period, Year, Week, and Data columns.

Originally I originally tried finding the “max” week number (which would be either 4 or 5 depending on period) using a =max(if) (formula found in column F) comparing the location, period, and year. But that formula would always spit out 5 since it looked at the entire data-set rather the specific location, period, and year.

I’m running office 16 which doesn’t have the maxifs capability yet.

Any help would be greatly appreciated!

I have a data-set that has weekly historical running total data. I have to do an analysis that ties in said weekly data with other data that was recorded monthly. In order to be able to do this I would have to pull data for the last week of each month, however, I am having trouble creating a formula that would do that.

I’ve attached a rudimentary example of the data set to this post.

The data-set has a Location, Period, Year, Week, and Data columns.

Originally I originally tried finding the “max” week number (which would be either 4 or 5 depending on period) using a =max(if) (formula found in column F) comparing the location, period, and year. But that formula would always spit out 5 since it looked at the entire data-set rather the specific location, period, and year.

I’m running office 16 which doesn’t have the maxifs capability yet.

Any help would be greatly appreciated!

Team,

The below formula searches for certain words and returns a different word when found. How would I add a counting function to the below formula in order to number the instances. For example, every time the search finds a "B0*", instead of just returning BELT it returns BELT1, and then BELT2 on the next occurance; and so on for the other search words?

=IF(ISNUMBER(SEARCH("B0*",B35,1))=TRUE,"Belt",IF(ISNUMBER(SEARCH("ACC",B35,1))=TRUE,"Accessory",IF(ISNUMBER(SEARCH("freight",B35,1))=TRUE,"Freight",IF(ISNUMBER(SEARCH("COC",B35,1))=TRUE,"Certificate",IF(ISNUMBER(SEARCH("cour",B35,1))=TRUE,"Courier",IF(ISNUMBER(SEARCH("ins",B35,1))=TRUE,"Insurance",IF(ISNUMBER(SEARCH("COM-",B35,1))=TRUE,"Commission","")))))))

]]>The below formula searches for certain words and returns a different word when found. How would I add a counting function to the below formula in order to number the instances. For example, every time the search finds a "B0*", instead of just returning BELT it returns BELT1, and then BELT2 on the next occurance; and so on for the other search words?

=IF(ISNUMBER(SEARCH("B0*",B35,1))=TRUE,"Belt",IF(ISNUMBER(SEARCH("ACC",B35,1))=TRUE,"Accessory",IF(ISNUMBER(SEARCH("freight",B35,1))=TRUE,"Freight",IF(ISNUMBER(SEARCH("COC",B35,1))=TRUE,"Certificate",IF(ISNUMBER(SEARCH("cour",B35,1))=TRUE,"Courier",IF(ISNUMBER(SEARCH("ins",B35,1))=TRUE,"Insurance",IF(ISNUMBER(SEARCH("COM-",B35,1))=TRUE,"Commission","")))))))

Tidings all!

I'm curious if someone knows of a simple way to do the following with the attached sheet.

I pull this report daily from a WMS and am comparing against a manual list of wave codes that ship from one facility. Whatever is not on this manual list, or the balance, ships from another warehouse.

I am able to filter out the duplicates simple enough, but as this report is based on lines rather than individual wave numbers, I'm not able to simple spot out duplicates.

What I'd like to do is compare column M against column K and if it finds a match, then highlight or fill in column L with the word 'Shelton'. I would then do this again comparing column N against column K and if it finds a match, filling in column L with TFC.

My goal would be to do this in minimal steps if possible so this could be run several times throughout the day.

I tried using formulas to highlight duplicates, but this didn't work due to multi-lines of the same wave number.

TIA!

I'm curious if someone knows of a simple way to do the following with the attached sheet.

I pull this report daily from a WMS and am comparing against a manual list of wave codes that ship from one facility. Whatever is not on this manual list, or the balance, ships from another warehouse.

I am able to filter out the duplicates simple enough, but as this report is based on lines rather than individual wave numbers, I'm not able to simple spot out duplicates.

What I'd like to do is compare column M against column K and if it finds a match, then highlight or fill in column L with the word 'Shelton'. I would then do this again comparing column N against column K and if it finds a match, filling in column L with TFC.

My goal would be to do this in minimal steps if possible so this could be run several times throughout the day.

I tried using formulas to highlight duplicates, but this didn't work due to multi-lines of the same wave number.

TIA!

Hi,

I have a column conditionally formatted to highlight in green if the cell is a positive value (>.01) and red if it is a negative value (<.01). That works well if I manually enter data into each cell.

But when I enter a formula into the cells in this column, they automatically highlight green even though the value is 0. I can't seem to figure out how to stop it. I'm talking about column L. Specifically, cell L11. I want cell L11 to be blank unless there is data in cell J11. But when I enter the formula, it highlights green. I've tried setting another highlight rule that if the cell = 0, to not fill. While that works, it removes the light blue highlight that the table gives every other row. And I'd like to keep every other row highlighted in light blue (with the exception of this column).

I've attached the spreadsheet here. I'd appreciate any help someone can give me on this.

Thank you.

I have a column conditionally formatted to highlight in green if the cell is a positive value (>.01) and red if it is a negative value (<.01). That works well if I manually enter data into each cell.

But when I enter a formula into the cells in this column, they automatically highlight green even though the value is 0. I can't seem to figure out how to stop it. I'm talking about column L. Specifically, cell L11. I want cell L11 to be blank unless there is data in cell J11. But when I enter the formula, it highlights green. I've tried setting another highlight rule that if the cell = 0, to not fill. While that works, it removes the light blue highlight that the table gives every other row. And I'd like to keep every other row highlighted in light blue (with the exception of this column).

I've attached the spreadsheet here. I'd appreciate any help someone can give me on this.

Thank you.

Hello all,

I am trying to find a way to highlight values that are repeated one after the other. For example:

1

2

3

3

3

3

2

4

3

4

5

The 4 3s are highlighted because they are consecutive. The other 3 is not so it isn't highlighted.

I've found things like =OR(A2=A1,A2=A3) But this works for 2 or more in a row, and I want only 4 or more consecutive to be highlighted.

]]>I am trying to find a way to highlight values that are repeated one after the other. For example:

1

2

3

3

3

3

2

4

3

4

5

The 4 3s are highlighted because they are consecutive. The other 3 is not so it isn't highlighted.

I've found things like =OR(A2=A1,A2=A3) But this works for 2 or more in a row, and I want only 4 or more consecutive to be highlighted.

Dear Master all,

I want to use a conditional formatting formula with 2 colors and for a blank table it matches the color of the table style and the results I want are in column F. Please help and I want the formula without the help column.

Thanks you

Roykana

I want to use a conditional formatting formula with 2 colors and for a blank table it matches the color of the table style and the results I want are in column F. Please help and I want the formula without the help column.

Thanks you

Roykana

I have 20 sets of lower and upper limits, with each set having a corresponding value. What i need is a formula that helps me find the corresponding value of my input by automatically looking it up within the ranges. The attachment has sample data to illustrate.

Thanks in advance for the help.

Thanks in advance for the help.

HI,

I have column B where user input the DATE.. sometime they add date in correct format and something wrong.

Correct format is = 02.02.2020

Wrong format is = 02-02-2020 or 02/02/2020

How can i check date and if it's in wrong format that Converts to right format by replacing "-" and "/" to ".".

So it should be through formula or functions.

kindly help

]]>I have column B where user input the DATE.. sometime they add date in correct format and something wrong.

Correct format is = 02.02.2020

Wrong format is = 02-02-2020 or 02/02/2020

How can i check date and if it's in wrong format that Converts to right format by replacing "-" and "/" to ".".

So it should be through formula or functions.

kindly help

Hi,

I want to run more then 1 excel formula from same column..

Means by

A formula which check the value of column A and B is:

HVIS(OG(A:A="";B:B="");"";HVIS(OG(A:A="";B:B<>"");"Missing Value in A";HVIS(OG(B:B="";A:A<>"");"Missing Value in B")))

This is another formula which run for same column but check some other thing.

HVIS(TÆL.HVISER($A:$A;A:A;$B:$B;"<>"&B:B)>0;"Same Seq No with different Dates";"")

Can i run both those formula from column X ?

So that column X will show me Error both if something is missing in column A or B and if something is not equal in column A+B then it will also show error in column X.

Is IT possible..??

]]>I want to run more then 1 excel formula from same column..

Means by

A formula which check the value of column A and B is:

HVIS(OG(A:A="";B:B="");"";HVIS(OG(A:A="";B:B<>"");"Missing Value in A";HVIS(OG(B:B="";A:A<>"");"Missing Value in B")))

This is another formula which run for same column but check some other thing.

HVIS(TÆL.HVISER($A:$A;A:A;$B:$B;"<>"&B:B)>0;"Same Seq No with different Dates";"")

Can i run both those formula from column X ?

So that column X will show me Error both if something is missing in column A or B and if something is not equal in column A+B then it will also show error in column X.

Is IT possible..??