SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2432962%22%20slang%3D%22en-US%22%3ECreate%20multiple%20if%20or%20statement%20but%20to%20result%20in%20a%20numerical%20number%20rather%20than%20a%20true%20or%20false%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2432962%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20banding%20table%20with%20two%20values%20which%20results%20in%20a%20certain%20rate%20being%20charged.%3C%2FP%3E%3CP%3EI%20then%20have%20a%20list%20of%20items%20and%20their%20values.%20I%20want%20to%20create%20an%20IF%20OR%20formula%20which%20follows%2C%20if%20the%20number%20on%20my%20table%20is%20greater%20than%20or%20equal%20to%20lower%20number%20on%20banding%20and%20less%20than%20or%20equal%20to%20higher%20number%20on%20banding%20THEN%20rate%20(value%20for%20being%20true)%2C%20OR%20if%20number%20on%20my%20table%20if%20less%20than%20or%20equal%20to%20lower%20on%20next%20value%20range%20and%20so%20forth.%20I%20want%20to%20create%20it%20so%20that%20the%20formula%20considers%20which%20value%20range%20is%20met%20and%20then%20provides%20the%20resulting%20rate%20on%20that%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20what%20I%20wrote%20and%20rather%20than%20giving%20me%20an%20output%20it%20conclude%20with%20%22True%22.%20The%20below%20examples%20has%20a%20value%20for%20I5%20of%2041%2C500%2C000%20and%20the%20value%20range%20between%20B2%20and%20C2%20is%2040%2C000%2C000%20to%2065%2C000%2C000%20hence%20the%20result%20of%20True.%3C%2FP%3E%3CP%3E%3DIF(I5%26gt%3B%3D'Hull%20Banding'!%24A%242%26lt%3B%3D'Hull%20Banding'!%24B%242%2C'Hull%20Banding'!%24C%242%2COR(I5%26gt%3B%3D'Hull%20Banding'!%24A%243%26lt%3B%3D'Hull%20Banding'!%24B%243%2C'Hull%20Banding'!%24C%243%2COR(I5%26lt%3B%3D'Hull%20Banding'!%24A%244%26lt%3B%3D'Hull%20Banding'!%24B%244%2C'Hull%20Banding'!%24C%244)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHELP%20please!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2432962%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2432977%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20multiple%20if%20or%20statement%20but%20to%20result%20in%20a%20numerical%20number%20rather%20than%20a%20true%20or%20false%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2432977%22%20slang%3D%22en-US%22%3ETry%3A%20%3DIF(I5%26gt%3B%3D'Hull%20Banding'!%24A%242%26lt%3B%3D'Hull%20Banding'!%24B%242%2C'Hull%20Banding'!%24C%242%2CIF(I5%26gt%3B%3D'Hull%20Banding'!%24A%243%26lt%3B%3D'Hull%20Banding'!%24B%243%2C'Hull%20Banding'!%24C%243%2CIF(I5%26lt%3B%3D'Hull%20Banding'!%24A%244%26lt%3B%3D'Hull%20Banding'!%24B%244%2C'Hull%20Banding'!%24C%244)))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2432979%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20multiple%20if%20or%20statement%20but%20to%20result%20in%20a%20numerical%20number%20rather%20than%20a%20true%20or%20false%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2432979%22%20slang%3D%22en-US%22%3Eit's%20now%20showing%20FALSE%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2433041%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20multiple%20if%20or%20statement%20but%20to%20result%20in%20a%20numerical%20number%20rather%20than%20a%20true%20or%20false%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2433041%22%20slang%3D%22en-US%22%3EAttach%20a%20sample%20file%20and%20I%20will%20put%20the%20equation%20in%20to%20test.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2433086%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20multiple%20if%20or%20statement%20but%20to%20result%20in%20a%20numerical%20number%20rather%20than%20a%20true%20or%20false%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2433086%22%20slang%3D%22en-US%22%3Ethanks%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1064156%22%20target%3D%22_blank%22%3E%40StoneKiwi%3C%2FA%3E%20for%20taking%20the%20time%20to%20look%20this%20over.%20I%20have%20attached%20a%20sample.%20I%20think%20excel%20is%20limited%20to%207%20multiple%20conditions%20within%20an%20IF%20statement%20so%20in%20the%20sample%2C%20may%20need%20to%20delete%20a%20couple.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2433125%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20multiple%20if%20or%20statement%20but%20to%20result%20in%20a%20numerical%20number%20rather%20than%20a%20true%20or%20false%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2433125%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1075182%22%20target%3D%22_blank%22%3E%40kimbirch2%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20the%20solution%20attached.%3C%2FP%3E%3CP%3EI%20moved%20the%20lookup%20table%20to%20the%20same%20sheet%20to%20make%20it%20easier%20for%20me%20to%20cell%20reference.%3C%2FP%3E%3CP%3EExplained%3A%3C%2FP%3E%3CP%3E%3DSUMPRODUCT(SUMIFS(%24H%242%3A%24H%2410%2C%24F%242%3A%24F%2410%2C%22%26lt%3B%22%26amp%3BB2%2C%24G%242%3A%24G%2410%2C%22%26gt%3B%22%26amp%3BB2))%3C%2FP%3E%3CP%3E%3DSUMPRODUCT(SUMIFS(Annualraterange%2CAgreedvaluefromrange%2C%22%26lt%3B%22%26amp%3BValue%2CAgreedvaluetorange%2C%22%26gt%3B%22%26amp%3BValue))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGood%20luck!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2433549%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20multiple%20if%20or%20statement%20but%20to%20result%20in%20a%20numerical%20number%20rather%20than%20a%20true%20or%20false%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2433549%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1075182%22%20target%3D%22_blank%22%3E%40kimbirch2%3C%2FA%3E%26nbsp%3BAs%20a%20variant%2C%20I%20added%20two%20alternative%20solutions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20With%20LOOKUP%2C%20though%20you%20need%20to%20sort%20your%20criteria%20in%20ascending%20order%3C%2FP%3E%3CP%3E2)%20With%20XLOOKUP%20(if%20your%20Excel%20version%20supports%20it)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2448589%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20multiple%20if%20or%20statement%20but%20to%20result%20in%20a%20numerical%20number%20rather%20than%20a%20true%20or%20false%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2448589%22%20slang%3D%22en-US%22%3EAmazing%20thanks%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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!

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)))
Attach a sample file and I will put the equation in to test.

Thanks
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

@kimbirch2 

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!

 

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

@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)