Excel Help with IF and Greater than less than

%3CLINGO-SUB%20id%3D%22lingo-sub-1645017%22%20slang%3D%22en-US%22%3EExcel%20Help%20with%20IF%20and%20Greater%20than%20less%20than%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1645017%22%20slang%3D%22en-US%22%3E%3CP%3ENot%20sure%20what%20the%20best%20formula%20is%20for%20this%2C%20any%20help%20or%20suggestions%20would%20be%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20see%20what%20I'm%20trying%20to%20accomplish%20with%20the%20formula%20I%20have%20so%20far.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFeel%20Free%20to%20download%20my%20attachment%20and%20figure%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20need%20the%20K%20column%2C%20once%20I%20have%20that%2C%20I%20can%20do%20the%20P%20myself.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22hookvikes_0-1599581601788.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F217037i7522E14E825324A8%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22hookvikes_0-1599581601788.png%22%20alt%3D%22hookvikes_0-1599581601788.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1645017%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1645371%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%20with%20IF%20and%20Greater%20than%20less%20than%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1645371%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F786022%22%20target%3D%22_blank%22%3E%40hookvikes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLOOKUP(K%2C%7B0%2C180%2C240%2C300%2C350%7D%2C%7B1%2C1%2C0.75%2C0.5%2C0%7D)*C17*Yield%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1645446%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%20with%20IF%20and%20Greater%20than%20less%20than%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1645446%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20is%20good%20help%2C%20but%20it%20only%20changes%20when%20those%20hit%20exactly.%3C%2FP%3E%3CP%3EI%20need%20LESS%20THAN%20180%2C%3C%2FP%3E%3CP%3EThen%20%3D%20or%20greater%20than%20180%20to%20less%20than%20240%3C%2FP%3E%3CP%3Ethen%20%3D%20or%20greater%20than%20240%20to%20Less%20than%20300%3C%2FP%3E%3CP%3Ethen%20%3D%20or%20greater%20than%20300%20to%20less%20than%20350%3C%2FP%3E%3CP%3Ethen%20%3D%20or%20greater%20than%20350%3C%2FP%3E%3CP%3Ethat%20way%20when%20the%20numbers%20hit%20in%20between%20any%20of%20these%2C%20i%20can%20get%20the%20correct%20calculation.%3C%2FP%3E%3CP%3EThanks%20for%20the%20help!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1645564%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%20with%20IF%20and%20Greater%20than%20less%20than%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1645564%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F786022%22%20target%3D%22_blank%22%3E%40hookvikes%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3EThe%20IF%20statement%20is%20missing%20AND%20functions%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3DIF(K%26lt%3B180%2CYield*C17%2C%3CBR%20%2F%3EIF(AND(K%26gt%3B%3D180%2CK%26lt%3B240)%2CC17*Yield%2C%3CBR%20%2F%3EIF(AND(K%26gt%3B%3D240%2CK%26lt%3B300)%2CC17*Yield*0.75%2C%3CBR%20%2F%3EIF(AND(K%26gt%3B%3D300%2CK%26lt%3B350)%2CC17*Yield*0.5%2C%3CBR%20%2F%3EIF(K%26gt%3B%3D350%2C0)))))%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20don't%20do%20that.%20This%20is%20a%20%22Nested%20IF%22%20statement%20which%20is%20notoriously%20hard%20to%20maintain%20and%20easy%20to%20get%20wrong.%20There%20is%20a%20better%20way.%20Use%20a%20table%20which%20you%20can%20easily%20update%20instead%20then%20replace%20that%20long%20nested%20IF%20statement%20with%20this%3A%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%26nbsp%3B%3CBR%20%2F%3E%3DYield*C17*VLOOKUP(K%2CtblFactors%2C2%2CTRUE)%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBelow%20is%20tblFactors.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Untitled.png%22%20style%3D%22width%3A%20129px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F217064i2A35AE29E4CFE35B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Untitled.png%22%20alt%3D%22Untitled.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20recommend%20using%20a%20table%20because%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20If%20the%20factor%20or%20K%20threshold%20changes%2C%20just%20change%20it%20in%20the%20table.%26nbsp%3B%3C%2FP%3E%3CP%3E2)%20If%20we%20want%20to%20add%20a%20500%20K%20threshold%2C%20we%20do%20not%20have%20to%20change%20the%20formula%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20create%20a%20table%2C%20enter%20it%20as%20you%20see%20here%20then%20select%20any%20value%20and%20press%20%3CSTRONG%3ECTRL-T%3C%2FSTRONG%3E.%20Check%20%3CEM%3EYes%20my%20table%20has%20headers%3C%2FEM%3E%26nbsp%3Bthen%20type%20%3CSTRONG%3EtblFactors%3C%2FSTRONG%3E%20in%20the%20name%20box%20located%20in%20the%20ribbon's%20left%20lower%20corner.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1645705%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%20with%20IF%20and%20Greater%20than%20less%20than%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1645705%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F786022%22%20target%3D%22_blank%22%3E%40hookvikes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20how%20it%20works%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20282px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F217071i5310297AE31F922E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Not sure what the best formula is for this, any help or suggestions would be appreciated!

 

You can see what I'm trying to accomplish with the formula I have so far.

 

Feel Free to download my attachment and figure out.

 

Just need the K column, once I have that, I can do the P myself.

 

hookvikes_0-1599581601788.png

 

5 Replies

@hookvikes 

That could be like

=LOOKUP(K,{0,180,240,300,350},{1,1,0.75,0.5,0})*C17*Yield

@Sergei Baklan 

That is good help, but it only changes when those hit exactly.

I need LESS THAN 180,

Then = or greater than 180 to less than 240

then = or greater than 240 to Less than 300

then = or greater than 300 to less than 350

then = or greater than 350

that way when the numbers hit in between any of these, i can get the correct calculation.

Thanks for the help! 

@hookvikes 
The IF statement is missing AND functions:

 

=IF(K<180,Yield*C17,
IF(AND(K>=180,K<240),C17*Yield,
IF(AND(K>=240,K<300),C17*Yield*0.75,
IF(AND(K>=300,K<350),C17*Yield*0.5,
IF(K>=350,0)))))

 

But don't do that. This is a "Nested IF" statement which is notoriously hard to maintain and easy to get wrong. There is a better way. Use a table which you can easily update instead then replace that long nested IF statement with this:

 
=Yield*C17*VLOOKUP(K,tblFactors,2,TRUE)

 

Below is tblFactors.

 

Untitled.png

 

I recommend using a table because:

 

1) If the factor or K threshold changes, just change it in the table. 

2) If we want to add a 500 K threshold, we do not have to change the formula

 

To create a table, enter it as you see here then select any value and press CTRL-T. Check Yes my table has headers then type tblFactors in the name box located in the ribbon's left lower corner.

 

Hope that helps

@hookvikes 

That's how it works

image.png

@Craig_Hatmaker 

Thanks that helped!

 

Think I've got the numbers coming out correctly now.