Need help an if/then formula.

[Oryan77]

I am not very good with formulas. I am not sure how to explain this formula, so I will do my best to describe what I am trying to do.

I have a sheet where cell K31 needs to display certain values depending on the values of cells K29 and D29.

The rules works as follows:

1. If the value of cell K29 is less than half of the value of cell D29, then display the value 10 in cell K31.

2. If the value of cell K29 is half of the value of cell D29, then display the value 20 in cell K31.

3. If the value of cell K29 is equal to the value of cell D29, then display the value 50 in cell K31.

4. If the value of cell K29 is 1-3 higher than the value of cell D29, then display the value 15 in cell K31. (example, K29 = 15 & D29 = 12, 13, or 14, then K31 has a value of 15.

5. If the value of cell K29 is 4-6 higher than the value of cell D29, then display the value 5 in cell K31.

6. If the value of cell K29 is 7 or higher than the value of cell D29, then display the value 0 in cell K31.

That's it. Thank you!

 

[Eric W]

What do you want if K29 > D29/2 but < D29?

Here are 2 options:

	D	K	L
17

<tbody>
[TD="align: center"]29[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]33[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]34[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]35[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]13[/TD]

[TD="align: center"]36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]16[/TD]

[TD="align: center"]37[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet15​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]K31[/TH]
[TD="align: left"]=IF(K29< D29/2,10,IF(K29=D29/2,20,IF(K29=D29,50,IF(K29<=D29+3,15,IF(K29<=D29+6,5,0)))))[/TD]
[/TR]
[TR]
[TH]K32[/TH]
[TD="align: left"]=LOOKUP(K29,L32:L37,{10,20,50,15,5,0})[/TD]
[/TR]
[TR]
[TH]L33[/TH]
[TD="align: left"]=D29/2[/TD]
[/TR]
[TR]
[TH]L34[/TH]
[TD="align: left"]=D29[/TD]
[/TR]
[TR]
[TH]L35[/TH]
[TD="align: left"]=D29+3[/TD]
[/TR]
[TR]
[TH]L36[/TH]
[TD="align: left"]=D29+6[/TD]
[/TR]
[TR]
[TH]L37[/TH]
[TD="align: left"]=D29+7[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



The K31 formula is a nested IF which should handle your requirements, but it doesn't handle the case I mention above. The K32 formula is a way to use a LOOKUP to get an easier formula, but it requires the helper cells in L32:L37 (which you can put anywhere).


Edit: Thanks, Fluff. Fixed it.

 

[Fluff]

Eric
It looks as though the board has "eaten" you K31 formula

 

[Oryan77]

What do you want if K29 > D29/2 but < D29?

Hey Eric, if K29 is less than D29 but greater than half of D29, it would always result in a value of 20.

If K29 is less than D29, but less than half of D29, it would always result in a value of 10.

Is that what you were asking about?

Oh, and using the helper cells would be perfectly fine. I can add them anywhere as you say.

 

[Eric W]

OK, given that, here are updated formulas:

	D	K	L
32
17

<tbody>
[TD="align: center"]29[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]33[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]34[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]35[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]36[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]37[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet15​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K31[/TH]
[TD="align: left"]=IF(K29< D29/2,10,IF(<fon
K29< D29,20,IF(K29=D29,50,IF(K29<=D29+3,15,IF(K29<=D29+6,5,0)))</fon
))[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]K32[/TH]
[TD="align: left"]=LOOKUP(K29,L32:L38,{10,20,50,15,5,0})[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L33[/TH]
[TD="align: left"]=D29/2[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L34[/TH]
[TD="align: left"]=D29[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L35[/TH]
[TD="align: left"]=D29+1[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L36[/TH]
[TD="align: left"]=D29+4[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]L37[/TH]
[TD="align: left"]=D29+7[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

You can use either one you prefer, they give the same results.

 

[Oryan77]

I ended up going with option 2 and it works perfectly. Thank you so much!

 

[Eric W]

After thinking about it a bit more, I realized that you could just use a VLOOKUP formula. Something like:

=VLOOKUP(K29,L32:M38,2)

where you'd just put the values you want next to the formulas in L32:L38. But as the saying goes, a working solution on time is better than a perfect solution too late! In any case, I'm glad it works for you.