How to use "if" formula for multiple conditions

**harryhu** · 04-06-2009, 06:01 PM

Hi all

I am trying to use if formula to satisfy 5 conditions, for example:
in column D i have all different percentages, but if the percentage is equal or greater than 4, then I want to return the value 5 to column E,
if it's equal or greater than 2 but smaller than 4, then column E should show up as 4,
if it's between -1 and 2, but smaller than 2, then column E should show up as 3,
if it's greater than -1 but smaller than -4, then column E should show up as 2,
if it's equal or greater than -4, then column E should show up as 1,

Any clue on how to satisfy these 5 conditions in 1 formula?

Much appreciated!

Harry

**daddylonglegs** · 04-06-2009, 06:10 PM

Hello Harry,

Is column D showing percentages or numbers?

If it's the former try this formula in E2 copied down

=MATCH(D2*100,{-9.9E+307,-4,-1,2,4})

If it's the latter remove *100

**harryhu** · 04-06-2009, 06:25 PM

That's awesome! Problem solved.
Thank you so much!

**Balaram** · 06-10-2011, 01:27 AM

I have a issue,

I was palnning for the depreciation statment where I wanted to give 6 if nested functions but the same is not giving any result but showing as it cannot give any result.

this is my formula,

{=IF($P9=0,if(R$6-$O9<0,0,IF(R$6-$O9>365,365,R$6-$O9),IF(R$6<$P9,IF(R$6-$O9>365,365,R$6-$O9),0))}

Where the P is the sale date coloumn,
R6 is the date cell
O6 is the date of the capitalisation,

Can any one help me out in the same.
Balaram.K.B

**abousetta** · 06-10-2011, 01:58 AM

Hello and welcome to the forum,

Please don't post question in other person's thread, but rather start a new thread and, if you feel this thread is relevant, you may link to it.

abousetta

**bumblebee24** · 06-29-2016, 10:20 AM

Hello all...

I am a power user of Excel for data, but am just starting to use conditional formatting at a new position. Trial and error with conditional formatting is a bit of a pain!

I want to apply multiple conditional formatting rules to one cell....is that possible?

b2 = a manually entered number
c2 = sum of columns d2 - f2 that are each manually entered

All the numbers in b2 and d2 - f2 could change multiple times as we forecast our plan. The ultimate goal is for a2 and b2 to equal. If they don't, I want one of two conditions to appear:
1. c2<b2 (red font)
2. c2>b2 (yellow highlight)

What I can't figure out is how to insert multiple if statements with multiple conditional formatting options based on the results of the if statements into one conditional formatting rule.

Is it possible to do?

Thanks in advance!!!

mult cond formats.jpg

**protonLeah** · 06-29-2016, 04:19 PM

bumblebee24,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.