The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!
Need help with nested formula
I have a worksheet that I calculate various states fees for my work. There are different rules for each state, for instance TX is the total sale X 0.1304348 or 20.00 flat fee whichever is greater. I created my workbook with a chart that has 5 rules, one rule in each column. I need to return the rule that is applicable. My issue is that some states have multiple rules like per square foot or % of sale whichever is greater, some have simple rules like just 15%. I need to look at each rule and determine if it is blank, which means I would move to rule 2 etc. You will note some say manual these need to return manual. In the case there are two rules that apply it is the greater.
This might make more since when you look at the sheet.
Experts.xlsm
This might make more since when you look at the sheet.
Experts.xlsm
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
frankly i could not figure out where is what, the spreadsheet is a mess.
may you please upload a clean version by indicating the precedent cells for formula with color and the manual correct desired solution for which you need to be done with formula
may you please upload a clean version by indicating the precedent cells for formula with color and the manual correct desired solution for which you need to be done with formula
One thought is to use a VLOOKUP to search for the two values, and if one is empty, use the other...something like this:
a1=VLOOKUP(StateCode, TableGrid, 2, false)
b1=VLOOKUP(StateCode, TableGrid, 3, false)
=IF(A1="", B1, A1)
Where StateCode is a cell reference to the state (e.g. Sheet2!A5) and TableGrid is the block that points to the data with column1 being the states, column 2 being the SQFT value and column3 being the % (e.g. TableGrid=Sheet3!$A$1:$C$5
Note: if you used named ranges, you won't have to put in all the cell references...if you don't know about named ranges, let me know.)
a1=VLOOKUP(StateCode, TableGrid, 2, false)
b1=VLOOKUP(StateCode, TableGrid, 3, false)
=IF(A1="", B1, A1)
Where StateCode is a cell reference to the state (e.g. Sheet2!A5) and TableGrid is the block that points to the data with column1 being the states, column 2 being the SQFT value and column3 being the % (e.g. TableGrid=Sheet3!$A$1:$C$5
Note: if you used named ranges, you won't have to put in all the cell references...if you don't know about named ranges, let me know.)
I used all named ranges. I am not sure I understand your solution. I was tying to write a formula that starts in rule one, and asks if it is blank, if so move to rule 2 and so on. If two rules are present, the greater of the two.
So extending what I had, it'll get somewhat complex but should do what I think you're asking:
=MAX(VLOOKUP(StateCode, TableGrid, 2, FALSE), VLOOKUP(StateCode, TableGrid, 3, FALSE))
This assumes that TableGrid is the block of cells that contain your State Codes in the first column, one value in the second column and the other value in the third column. You can change the 2 and 3 as needed to be other columns, but the first column will need to be the list of state codes that you're looking up.
StateCode contains a valid code in the first column of TableGrid.
Attached is a sample file
Pick1Of2.xlsx
=MAX(VLOOKUP(StateCode, TableGrid, 2, FALSE), VLOOKUP(StateCode, TableGrid, 3, FALSE))
This assumes that TableGrid is the block of cells that contain your State Codes in the first column, one value in the second column and the other value in the third column. You can change the 2 and 3 as needed to be other columns, but the first column will need to be the list of state codes that you're looking up.
StateCode contains a valid code in the first column of TableGrid.
Attached is a sample file
Pick1Of2.xlsx
Thank you for your submission, My issue is not the lookup, it is about the test. I use all named ranges, so when I do am looking at a starting place, I look use a drop down list of the states (see d53) and I want to start with rule_1, since all of the states are named references as are each of the rules, I use the following:
(INDIRECT(D53) Lot_Rule_1)
This gives me my look up, which is the intersection of the state and the rule.
With this is my starting point, I try to determine the following:
Is Lot_rule_1 blank? If yes, go to Lot_Rule_2, if no, return the value of Lot_rule_1;
Next test, If yes is Lot_rule_2 blank? If yes, ask Is Lot rule 1 and 2 are blank, go to lot_rule 3; if no, return the value of the greater, Lot_rule_1 or 2 (PropPrice*Lot_rule1) or (Target1*Lot_Rule_2); if no, then goo to lot rule three and return the value of Lot_Rule_3 (Target2 *Lot_rule_three); Sometimes Lot_rule 1 is not blank and lot_rule_2 is blank but Lot_rule 3 is not, then I would need to determine which is greater.
Lot Rule1 always is a percentage of PropPrice (amount of the sale)
Lot Rule 2 is always a calculation of Target1(the number of spaces)*Lot rule 2
Lot Rule 3 is always a calculation of Target2 (the square footage)*Lot Rule 3
Explaining this makes it clearer in my mind, my hope is that others will understand!
Please note that I discovered I had not given a named range to PropPrice, I put it on my sheet as B50, I also note target1 is J58 and Target 2 is J51.
I am attaching file with those changes.
Experts-V3.xlsm
(INDIRECT(D53) Lot_Rule_1)
This gives me my look up, which is the intersection of the state and the rule.
With this is my starting point, I try to determine the following:
Is Lot_rule_1 blank? If yes, go to Lot_Rule_2, if no, return the value of Lot_rule_1;
Next test, If yes is Lot_rule_2 blank? If yes, ask Is Lot rule 1 and 2 are blank, go to lot_rule 3; if no, return the value of the greater, Lot_rule_1 or 2 (PropPrice*Lot_rule1) or (Target1*Lot_Rule_2); if no, then goo to lot rule three and return the value of Lot_Rule_3 (Target2 *Lot_rule_three); Sometimes Lot_rule 1 is not blank and lot_rule_2 is blank but Lot_rule 3 is not, then I would need to determine which is greater.
Lot Rule1 always is a percentage of PropPrice (amount of the sale)
Lot Rule 2 is always a calculation of Target1(the number of spaces)*Lot rule 2
Lot Rule 3 is always a calculation of Target2 (the square footage)*Lot Rule 3
Explaining this makes it clearer in my mind, my hope is that others will understand!
Please note that I discovered I had not given a named range to PropPrice, I put it on my sheet as B50, I also note target1 is J58 and Target 2 is J51.
I am attaching file with those changes.
Experts-V3.xlsm
>Your comment
"Is Lot_rule_1 blank? If yes, go to Lot_Rule_2, if no, return the value of Lot_rule_1;
Next test, If yes is Lot_rule_2 blank? If yes, ask Is Lot rule 1 and 2 are blank, go to lot_rule 3; if no, return the value of the greater, Lot_rule_1 or 2 (PropPrice*Lot_rule1) or (Target1*Lot_Rule_2); if no, then goo to lot rule three and return the value of Lot_Rule_3 (Target2 *Lot_rule_three); Sometimes Lot_rule 1 is not blank and lot_rule_2 is blank but Lot_rule 3 is not, then I would need to determine which is greater.
Lot Rule1 always is a percentage of PropPrice (amount of the sale)
Lot Rule 2 is always a calculation of Target1(the number of spaces)*Lot rule 2
Lot Rule 3 is always a calculation of Target2 (the square footage)*Lot Rule 3
"
--
>Response:
I'm not quite sure what that means,
It seems like if there's a rule1 value, show it (even if there's a rule 2): " if no [not blank], return the value of Lot_rule_1"
Otherwise if there's both rule1 and rule2, show the greater. (this is in conflict with the first statement)
If there's only rule2, show it.
Where does rule 3 fit into this?
And at this point, my above vlookup will get the job done.
Put this into cells as follows:
D55 =VLOOKUP(D53,$A$6:$M$46,8,
D56 =VLOOKUP(D53,$A$6:$M$46,9,
D57 =VLOOKUP(D53,$A$6:$M$46,10
These will be the values of lot1, lot2, lot3, based on what state is selected in D53
Next you can apply your rules, something like this:
D58 =IF(D55<>0, D55, D56)
Maybe you want rule 1 if rule2 is blank; rule 2 if rule 1 is blank, and neither is blank, show the larger:
=IF(D55<>0, IF(D56<>0, MAX(D55, D56), D55), D56)
i.e. if Lot1 is not empty, check Lot2; if Lot2 is also not empty then take the larger otherwise use Lot1. However, if Lot1 is empty, use Lot2
Does Lot3 get used if the first 2 are empty?:
=IF(D55<>0, IF(D56<>0, MAX(D55, D56), D55), IF(D56<>0, D56, D57))
"Is Lot_rule_1 blank? If yes, go to Lot_Rule_2, if no, return the value of Lot_rule_1;
Next test, If yes is Lot_rule_2 blank? If yes, ask Is Lot rule 1 and 2 are blank, go to lot_rule 3; if no, return the value of the greater, Lot_rule_1 or 2 (PropPrice*Lot_rule1) or (Target1*Lot_Rule_2); if no, then goo to lot rule three and return the value of Lot_Rule_3 (Target2 *Lot_rule_three); Sometimes Lot_rule 1 is not blank and lot_rule_2 is blank but Lot_rule 3 is not, then I would need to determine which is greater.
Lot Rule1 always is a percentage of PropPrice (amount of the sale)
Lot Rule 2 is always a calculation of Target1(the number of spaces)*Lot rule 2
Lot Rule 3 is always a calculation of Target2 (the square footage)*Lot Rule 3
"
--
>Response:
I'm not quite sure what that means,
It seems like if there's a rule1 value, show it (even if there's a rule 2): " if no [not blank], return the value of Lot_rule_1"
Otherwise if there's both rule1 and rule2, show the greater. (this is in conflict with the first statement)
If there's only rule2, show it.
Where does rule 3 fit into this?
And at this point, my above vlookup will get the job done.
Put this into cells as follows:
D55 =VLOOKUP(D53,$A$6:$M$46,8,
D56 =VLOOKUP(D53,$A$6:$M$46,9,
D57 =VLOOKUP(D53,$A$6:$M$46,10
These will be the values of lot1, lot2, lot3, based on what state is selected in D53
Next you can apply your rules, something like this:
D58 =IF(D55<>0, D55, D56)
Maybe you want rule 1 if rule2 is blank; rule 2 if rule 1 is blank, and neither is blank, show the larger:
=IF(D55<>0, IF(D56<>0, MAX(D55, D56), D55), D56)
i.e. if Lot1 is not empty, check Lot2; if Lot2 is also not empty then take the larger otherwise use Lot1. However, if Lot1 is empty, use Lot2
Does Lot3 get used if the first 2 are empty?:
=IF(D55<>0, IF(D56<>0, MAX(D55, D56), D55), IF(D56<>0, D56, D57))
Premium Content
You need an Expert Office subscription to comment.Start Free Trial