Nested functions not calculating with"text" or providing value... HELP!

Hello all,

I created a "calculator" to determine the lesser of value (I8) out of three different values (F7, F8, & F9), so that I can compare that smallest value (I8/F12) to the value I have on file (F15) for each client.
If there is a difference in those values, I need to know if it exists (I12) and the value of that difference, taking into account two threshold values (I13).

My calculator works just fine for each "threshold value" I am using.

However, when I attempt to combine my formulas, I get a #VALUE! error for GAP Required, which I am assuming it's because of the text in the string and a TRUE value for the Amount Required, when I'm actually looking for a numeric value.

Calculator Tab, $5000 threshold.
I12

Please Login or Register  to view this content.

I13

Please Login or Register  to view this content.

RMS Tab, $1000 Threshold
I12

Please Login or Register  to view this content.

I13

Please Login or Register  to view this content.

This are the formula's I combined that are proving to be troublesome, Sheet 1:
I12

Please Login or Register  to view this content.

I13

Please Login or Register  to view this content.

For purposes of this post, my attachment has THREE tabs. Tab 1 - Calculator with $5,000 threshold for specific account numbers. Tab 2 - Calculator with $1,000 threshold for specific account numbers. Tab 3 - Calculator with formulas above and combined formula as well to test.

Lesser Of Calculator 2.xlsx

Thank you for any and all help!

The AND should be OR and in the following format:

OR(D4=2301,D4=2303,D4=2304,D4=2305,D4=6900)

You need to revise all the formulae

=IF(OR(AND(D4=2301,2303,2304,2305,6900),AND(D4=3057,3058,3059,3060,3061,3062,3063,3069,3070,3071,3072,3073,3074,3075,3101,3164,3212,4810,4814,7358,7359,7360)),OR(IF((F15+5000)<F12,"Yes","N/A"),IF((F15+1000)<F12,"Yes","N/A")),OR(IF(F15<F12,"Yes","N/A"),IF(F15<F12,"Yes","N/A")))

Not sure what the above is trying to do with all the values for D4 ???? but AND conditions need to be OR.

Thanks for your reply JohnTopley.

All the different values are needed because when I use this calculator, I need to specify the account.

Clients 2301,2303,2304,2305,6900 have a threshold of $5k.
Clients 3057,3058,3059,3060,3061,3062,3063,3069,3070,3071,3072,3073,3074,3075,3101,3164,3212,4810,4814,7358,7359,7360 have a threshold of $1k.
All my other clients DO NOT have thresholds when determining flood coverage.

So for instance, if I enter any of the client numbers in the first set above, it should calculate if GAP is required taking into account the $5k.
i.e. Lesser Of = 125,000. LKFCA = 120000. Because the difference in coverage is at $5k, GAP required will reflect no, and no value will populate for coverage amount.

If I enter any of the client numbers in the second set above, it should calculate if GAP is required taking into account the $1k.
i.e. Lesser Of = 125,000. LKFCA = 124000. Because the difference in coverage is at $1k, GAP required will reflect no, and no value will populate for coverage amount.

I need to somehow combine my already working formulas to take into account clients in set #1 and their $5k threshold, or clients in set #2 and their $1k threshold, or clients without a threshold, which would just calculate the difference between the Lesser Of and LKFCA.

Can you explain what you mean by all the AND need to be ORs?

Hope this helps.

To select a client it has to be an OR condition

i,e, Client A OR Client B OR Client C as a client cannot be A AND B AND C.

Consider creating a table of clients with their corresponding thresholds and Use VLOOKUP to determine the limit. If client is not in the table "error" condition - they are by default the clients without a threshold.

Look at VLOOKUP function.

See the attached.

I have used a table of clients (named range "Client_tbl")and categorised them as "1" or "2" then used VLOOKUP to determine what is required. You check these out because I found the AND/OR formulae rather confusing.

In Sheet1 E19

=IF(IFERROR(VLOOKUP($D$4,Client_Tbl,2,0)=1,"N/A"),IF($F$15+5000<$F$12,"Yes",IF($F$15<$F$12,"Yes","N/A")),"")

in E20

=IFERROR(IF(AND(VLOOKUP($D$4,Client_Tbl,2,0)=1,$I$12="Yes"),$F$12-$F$15-5000,$F$12-$F$15),"N/A")

in E28

=IFERROR(IF(VLOOKUP($D$4,Client_Tbl,2,0),IF($F$15+5000<$F$12,"Yes",IF($F$15+1000<$F$12,"Yes",IF($F$15<$F$12,"Yes",IF($F$15<$F$12,"Yes","N/A"))))),"N/A")

in E29

=IFERROR(IF(AND(VLOOKUP($D$4,Client_Tbl,2,0)=1,$I$12="Yes"),$F$12-$F$15-5000,IF(AND(VLOOKUP($D$4,Client_Tbl,2,0)=1,$I$12="Yes"),$F$12-$F$15-5000,$F$12-$F$15)),"")