SOLVED

# Create multiple if or statement but to result in a numerical number rather than a true or false

Occasional Contributor

# Create multiple if or statement but to result in a numerical number rather than a true or false

Hi,

I have a banding table with two values which results in a certain rate being charged.

I then have a list of items and their values. I want to create an IF OR formula which follows, if the number on my table is greater than or equal to lower number on banding and less than or equal to higher number on banding THEN rate (value for being true), OR if number on my table if less than or equal to lower on next value range and so forth. I want to create it so that the formula considers which value range is met and then provides the resulting rate on that value.

This is what I wrote and rather than giving me an output it conclude with "True". The below examples has a value for I5 of 41,500,000 and the value range between B2 and C2 is 40,000,000 to 65,000,000 hence the result of True.

=IF(I5>='Hull Banding'!\$A\$2<='Hull Banding'!\$B\$2,'Hull Banding'!\$C\$2,OR(I5>='Hull Banding'!\$A\$3<='Hull Banding'!\$B\$3,'Hull Banding'!\$C\$3,OR(I5<='Hull Banding'!\$A\$4<='Hull Banding'!\$B\$4,'Hull Banding'!\$C\$4)))

8 Replies

# Re: Create multiple if or statement but to result in a numerical number rather than a true or false

Try: =IF(I5>='Hull Banding'!\$A\$2<='Hull Banding'!\$B\$2,'Hull Banding'!\$C\$2,IF(I5>='Hull Banding'!\$A\$3<='Hull Banding'!\$B\$3,'Hull Banding'!\$C\$3,IF(I5<='Hull Banding'!\$A\$4<='Hull Banding'!\$B\$4,'Hull Banding'!\$C\$4)))

# Re: Create multiple if or statement but to result in a numerical number rather than a true or false

it's now showing FALSE

# Re: Create multiple if or statement but to result in a numerical number rather than a true or false

Attach a sample file and I will put the equation in to test.

Thanks

# Re: Create multiple if or statement but to result in a numerical number rather than a true or false

thanks @StoneKiwi for taking the time to look this over. I have attached a sample. I think excel is limited to 7 multiple conditions within an IF statement so in the sample, may need to delete a couple.
best response confirmed by kimbirch2 (Occasional Contributor)
Solution

# Re: Create multiple if or statement but to result in a numerical number rather than a true or false

I moved the lookup table to the same sheet to make it easier for me to cell reference.

Explained:

=SUMPRODUCT(SUMIFS(\$H\$2:\$H\$10,\$F\$2:\$F\$10,"<"&B2,\$G\$2:\$G\$10,">"&B2))

=SUMPRODUCT(SUMIFS(Annualraterange,Agreedvaluefromrange,"<"&Value,Agreedvaluetorange,">"&Value))

Good luck!

# Re: Create multiple if or statement but to result in a numerical number rather than a true or false

Absolutely amazing! Thank you so much. I hadn't considered to format it in this way.

# Re: Create multiple if or statement but to result in a numerical number rather than a true or false

@kimbirch2 As a variant, I added two alternative solutions.

1) With LOOKUP, though you need to sort your criteria in ascending order

2) With XLOOKUP (if your Excel version supports it)

# Re: Create multiple if or statement but to result in a numerical number rather than a true or false

Amazing thanks @Riny_van_Eekelen