asked on

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

Professor J

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

rspahitz

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

farrissf

ASKER

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.

rspahitz

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

farrissf

ASKER

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

rspahitz

I'll tty to get to this later today

ASKER CERTIFIED SOLUTION

rspahitz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

farrissf

ASKER

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 Liss

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.