help with simple conditional formatting formula, I think

[juntjoo]

http://img-cdn.filefactory.com/embed...xmecuc72i3.png

unless I made it more complicated than possible. IDK. Hopefully one of you can tell me how to fix it.

Basically if A1 is 110% or less than B1 it should be red,

then towards being 150% it should move through white towards green. But as you can see it's green at only 100%.

What am I doing wrong? Thanks

p.s. I can't upload pics to this site by any way it offers nor can I use the paperclip button. must be some javascript problem.

[FlameRetired]

Hi juntjoo.

That paperclip icon hasn't worked for some time.

To attach a file to your post,

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

[juntjoo]

thank you! check it out now if ya could

[FlameRetired]

I deleted the current CF. Then applied 2 CFs to B1 using the "use a formula to determine which cells to format". Then these two formulas.

For green =$A$1/$B$1<1.1 using fill and fill effects.

For red ... and I suspect this is what you are really wanting ... =$A$1/$B$1>1.5 also using fill and fill effects.

See attached.

[FDibbins]

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

[juntjoo]

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

I thought I did attach a sample file

[AliGW]

I thought I did attach a sample file

You did.

Ford - it is right underneath the image in the opening post.

[juntjoo]

Thanks, tho do you have any idea why mine's not working? Should it not? It is simpler and how I'd logically go about setting this up with just one rule, so I'd like to know if I'm doing something wrong. I'd use your way if there was no other so I just want to figure out what is wrong with my method.

[AliGW]

Yours can't possibly work, as far as I can see: whatever is less than 1.1 is also less than 1.5, so there is no real distinction. What exactly are you expecting it to do?

[juntjoo]

oh thanks, I missed I had the less than sign in there twice, the one for green was supposed to be more than(>), so I just changed that and made the numbers 1 to 1, 1.0 which should make the cell red right? Not working for me thus far.

[juntjoo]

I think you meant b1. I thought my conditional formatting was checking for the result of the formula of a1/b1, which would be 1. I put the results in the a1 for easy reference as to the result of the formula, showing 1, which is less than 1.1, which should return a red cell no?

AliG as is sacha cohen? nice reference, unless it's the real alig, either way love him and his characters

[AliGW]

I'm not AliG - I am AliGW, which relates to my name. I do not care for Sacha Baron Cohen, to be honest.

[juntjoo]

nooo... really? must be too immature, how about ellen degeneres?

Anyway, so I tried it with a more simple example with a formula for adding the two cells and applying the formatting to the result cell.

*crossing fingers that attaching image and file worked...

D1 should be red!

[juntjoo]

Okay, I've made it even simpler. File is attached if you need to play with it but pic shows what seems to me to be a simple conditional formatting forumula and 0 should yield a green cell no?

[FlameRetired]

juntjoo,

Unless the value in D1 is affected by A1+B1 (it's not explicitly stated in the instructions) I don't think this is doable by color scale.

If the value of D1 is affected by A1+B1 then you could test the resulting value(s) in D1 directly. That should be doable and by implication fulfill the A1+B1 rule.

So in your real workbook how is the value of D1 determined?

I'd use your way if there was no other so I just want to figure out what is wrong with my method.

It may simply be a matter of misapplication. See if there is a way to affect the value of D1 and test that.

[juntjoo]

yes it is affected by A1+B1. Inside D1 is "=$A$1+$B$1"

The original workbook that started this all is a bit more complicated but working through this I've simplified it and my sample workbook here is basically demonstrating the problem.

Not sure what you're getting at but shouldn't D1 be green? Thanks


There would be no "D1" in my original workbook. Just A1 and B1 and I'd rather not create another cell with a formula to relate the two cells. I'd rather use the formula within the conditional formatting rule if possible. If this isn't possible then I'd have to create a "D1" cell in my workbook and create a formatting rule based directly on the D1 results, which would be simple, no formula, just formatting based on numbers I specify. But I'm trying to keep things tidy and not include an extra D1 cell.

so if this isn't possible, how is the formula option in conditional formatting supposed to be used? lol, is my request not reasonable Excel??

Thanks

[juntjoo]

So formulas don't work with color grades? Is that it?

[FDibbins]

I thought I did attach a sample file

Apologies, apparently I missed that

[FlameRetired]

There would be no "D1" in my original workbook. Just A1 and B1 and I'd rather not create another cell with a formula to relate the two cells. I'd rather use the formula within the conditional formatting rule if possible. If this isn't possible then I'd have to create a "D1" cell in my workbook and create a formatting rule based directly on the D1 results, which would be simple, no formula, just formatting based on numbers I specify. But I'm trying to keep things tidy and not include an extra D1 cell.

All posts thus far indicate you want CF applied to D1.

Do you still want that?

What am I missing?