conditional formatting

[monere]

Hi,

I need to create something which I don't know how to explain, and which I don't know how to do, but which I believe to have something to do with conditional formatting

Here's what I need to do...

Cell A1 sums up the values of cells A2 - A32, and returns the total number (duh!). And cell B1 sums up the values of cells B2 - B32, and returns the total number. This, I know how to do, so no problem here.

Now, here is the tricky part: I want another cell (doesn't matter which one, as long as its not one of those in use) to output a number highlighted in green, yellow, or red, based on these conditions:

- if value of A1 cell is ±2% of B1 cell's value, then output number must be highlighted in yellow
- if value of A1 is +3% (and higher) of B1 cell's value, then output number must be highlighted in red
- if value of A1 is -3% (and lower) of B1 cell's value, then output number must be highlighted in green.

Yeah, this is what I need to do, but I don't know how to use the conditional formatting properly in order to accomplish this. I have a hunch that I must choose "formula" as the type of formatting, but I'm not sure. And I don't know what the formula for this is anyway.

So, can someone please share a tip or two? Please!

 

[Takae]

Hi,
If I misunderstand, sorry.Hope this helps.

yellow =AND($B$1*0.98<=$A$1,$B$1*1.02>=$B$1)
red =$B$1*1.03<=$A$1
green =$B$1*0.97>=$A$1

(e.g)
A1=98, B1=100 yellow
A1=102, B1=100 yellow
A1=over 103, B1=100 red
A1=under 97, B1=100 green

 

[monere]

Hi,
If I misunderstand, sorry.Hope this helps.

yellow =AND($B$1*0.98<=$A$1,$B$1*1.02>=$B$1)
red =$B$1*1.03<=$A$1
green =$B$1*0.97>=$A$1

(e.g)
A1=98, B1=100 yellow
A1=102, B1=100 yellow
A1=over 103, B1=100 red
A1=under 97, B1=100 green

It won't work. It says "enter a valid formula.".... I took a screenshot of the error if you need it, but I don't know how to attach it to this reply :D

Also, I forgot to mention that I have excel 2013?? I believe that this is the last version of excel, and I have the last version anyway...

But, thanks for trying to help

 

[Takae]

My understanding is not wrong?

The formula to input are only highlighted.
Then, you select format.
yellow =AND($B$1*0.98<=$A$1,$B$1*1.02>=$B$1)
red =$B$1*1.03<=$A$1
green =$B$1*0.97>=$A$1

 

[monere]

My understanding is not wrong?

The formula to input are only highlighted.
Then, you select format.
yellow =AND($B$1*0.98<=$A$1,$B$1*1.02>=$B$1)
red =$B$1*1.03<=$A$1
green =$B$1*0.97>=$A$1

I think you understood correctly. It's just that the formulas you gave me don't work. Unfortunately, I can't send you the screenshot of the error right now as I just got to work, and the file I'm talking about is at my home. When I get home (that would be in about 8-9 hours from now) I can attach the file to see the error message yourself, but like I said, I don't know how to attach the file. I can't see any "attach", or "attachment" button anywhere. I think I'll upload it as an image to image hosting sites and post the link here.

Also, A and B columns would be C si E in my case, cause that's the columns I will reference, but this shouldn't matter because I properly replaced the letters in your formulas