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
farrissfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Professor JMicrosoft Excel ExpertCommented:
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
rspahitzCommented:
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$50)

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.)
farrissfAuthor Commented:
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.
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Learn More!
rspahitzCommented:
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
farrissfAuthor Commented:
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
rspahitzCommented:
I'll tty to get to this later today
rspahitzCommented:
>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,FALSE)
D56 =VLOOKUP(D53,$A$6:$M$46,9,FALSE)
D57 =VLOOKUP(D53,$A$6:$M$46,10,FALSE)

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))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
farrissfAuthor Commented:
I have perfected my "rules chart" and will start working on it again tomorrow.  I like the last suggestion and I am going to try that first.  I will let you know, thank you again for your response.
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.