Need help an if/then formula.

Oryan77

Board Regular
Joined
May 9, 2009
Messages
176
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!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
What do you want if K29 > D29/2 but < D29?

Here are 2 options:

DKL
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.
 
Last edited:
Upvote 0
Eric
It looks as though the board has "eaten" you K31 formula
 
Upvote 0
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.
 
Last edited:
Upvote 0
OK, given that, here are updated formulas:

DKL
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,262
Messages
6,171,080
Members
452,377
Latest member
bradfordsam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top