Jun 09 2021 04:05 PM - edited Jun 09 2021 05:31 PM
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!
Jun 09 2021 04:12 PM
Jun 09 2021 04:16 PM
Jun 09 2021 05:18 PM
Jun 09 2021 05:32 PM
Jun 09 2021 06:17 PM
SolutionPlease 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!
Jun 09 2021 07:09 PM
Jun 09 2021 10:24 PM
@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)
Jun 15 2021 05:57 AM
Jun 09 2021 06:17 PM
SolutionPlease 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!