Sep 08 2020 09:15 AM - edited Sep 08 2020 09:51 AM
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.
Sep 08 2020 10:44 AM
Sep 08 2020 11:20 AM
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!
Sep 08 2020 11:48 AM
@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
Sep 08 2020 12:29 PM
Sep 08 2020 01:51 PM