Forum Discussion
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)))
HELP please!
Please find the solution attached.
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!
8 Replies
- 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)))
