Forum Discussion

hookvikes's avatar
hookvikes
Copper Contributor
Sep 08, 2020

Excel Help with IF and Greater than less than

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.

 

 

Formulas and Functions

5 Replies

Sort By
  • Craig_Hatmaker's avatar
    Craig_Hatmaker
    Copper Contributor
    Sep 08, 2020

    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.

     

     

    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's avatar
      hookvikes
      Copper Contributor
      Sep 08, 2020

      SergeiBaklan 

      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! 

Resources