IF Statement using tiered values

[Bazzza67]

Hi Board Members

I am seeking assistance for an If formula, that is not quite functioning as I would like in two instances

For the image examples above, the input cell is B1, this must be a positive integer >1 and B7 is either "Yes" or "No"

In B7, if "NO" is selected I would like cell C7 to show 0.00

If B7 is "YES", I would like cell C7 to show a price based on the value in B1 using the tiered pricing
in cell range B11:C15. Up to 5 = 23.00, 6-20 is £34.75 and so on.

I have tested all the positive integers in B1, all appear to function correctly and show the correct price in C7
except when 20 is entered.

Also, if "NO" is selected in B7, C7 shows "FALSE"

The formal I have used in C7 is
=IF(AND(B7="Yes",B1<5.1),D11,IF(AND(B7="Yes",B1>5,B1<20),D12,IF(AND(B7="Yes",B1>20,B1<51),D13,IF(AND(B7="Yes",B1>50),D14,IF(AND(B7="No",B1>0),D15)))))

Any help would be greatly appreciated

Thank you in anticipation!

 

[SanjayGMusafir]

What version of Excel are you using - Update here as well as in your profile.

Also, upload sample data using XL2BB utility to better understand what is going wrong

 

[Joe4]

A few things:

The IF funtion has 3 arguments:
=IF(condition,what to return if condition is true,what to return if condition is false)

The last argument (what to return if condition is FALSE) is optional, but if you leave it out, and it the condition is false, it will then return FALSE.

In a nested IF, you usually put the next IF in this FALSE argument. However, in the last if, you usually put what you want to return if it is FALSE.
So instead of ending like:
...,D15)))))
I think you actually want it to end like this (so it returns 0 instead of FALSE).
...,D15,0)))))

Regarding when B1 is exactly 20, the issue is you haven't account for it!
Note the conditions on the B1 value:
...IF(AND(B7="Yes",B1>5,B1<20),D12,IF(AND(B7="Yes",B1>20)...

Since you wanted the condition of "6 up to 20" (meaning, 20 should be included), it should be:
...IF(AND(B7="Yes",B1>5,B1<=20),D12,IF(AND(B7="Yes",B1>20)...
(note the "<=" sign)

 

[NateSC]

Keep in mind that an "If" will quit looking at the first condition that is satisfied. So test for "No" first so you don't have to test B7 for any subsequent statements. Also, you can then just test for the max value for each tier from lowest to highest. Finally, if you have a newer version of Excel, you can use "IFS" instead of a nested structure. It makes these types of statements much more concise:

=IFS(B7="No",0,B1<=5,C11,B1<=20,C12,B1<=50,C13,B1>50,C14)

Here is the nested version:

=IF(B7="No",0,IF(B1<=5,C11,IF(B1<=20,C12,IF(B1<=50,C13,C14))))

(I am guessing that B7 can only be "Yes" or "No". If there are other possible selections, you will need your "And" statements.)

 

[Bazzza67]

Thank you so much Joe4 & NateSC.

NateSC...you are correct, the only available options in B7 is indeed "Yes" or "No"....the person I am creating this for has formatted B7 as a two-option "Yes or No" drop-down box. I would like the formula to work from the drop-down selection but it currently doesn't. If I change to a non-drop down cell and enter either "Yes" or "No" it does work.

Can the formula =IF(B7="No",0,IF(B1<=5,C11,IF(B1<=20,C12,IF(B1<=50,C13,C14)))) be amended to deal with this?

Thank you

 

[Joe4]

Is the drop-down a simple Excel Validation drop-down box?
If so, it should work without having to do anything special. It should work the same as typing "Yes" or "No" in the box.

If this is what you are using, and it is not working, make sure that you do not have any extra spaces at the beginning or ending of the "Yes" or "No" values.
And make sure the Automatic Calculations are turned on.

If you are using drop-down boxes form Forms or ActiveX controls, then it probably won't work unless you are actually mapping the value from the form to cell B7.

 

[Bazzza67]

Hi Joe4

Yes, I believe it is a simple Excel validation drop-down box. By selecting "No" from the drop-down the formula works, but shouldn't, it should only work if "Yes" is selected. When I try to manually type "No" in the box I get a Microsoft Excel dialogue box "This value doesn't match the data validation restrictions defined for the cell"

I will have to leave until the morning now, and check with the spreadsheet author, to see if there are any spaces after Yes & No in the validation. I believe Auto Calculation is already switched on.

Thanks again for your assistance...I will post an update tomorrow either way

 

[Bazzza67]

##UPDATE
OK. I gained access to the Data Validation source and used the TRIM function to remove any spaces, The formula provided NatesSc now works perfectly

Many thanks again!