I think I developed a brain lock. I have a conditional format formula comparing 2 cells. I need it to color red if the difference is >3¢ or < 3¢.
Here is what I have: =(D19-F19)<>3
Can someone please tell me what I am doing wrong.
Posted this but no answer yet.
https://www.mrexcel.com/forum/excel-...-new-post.html
Thank you.
What's in D:F? Is it currency ($0.00) or are you counting in pennies?
3¢ = $0.03
Ben Van Johnson
< > means 'not equal to'
What you want is:
Formula:
Please Login or Register to view this content.
If the figures are in € / $, then you'll probably want 0.03 instead of 3.
Regards,
Aardigspook
I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
You don't need to give me rep if I helped, but a thank-you is nice.
D19 and F19 are both pennies. If the difference is >+3¢ or greater than -3¢, I need it to color red.
Thanks
Please Login or Register to view this content.
That worked on D19-F19 as the difference in the formula is 14¢, but when I copied it to F19-H19 it made it red, but the difference between those 2 cells was zero.
Any other ideas?
Please Login or Register to view this content.
Last edited by protonLeah; 02-04-2017 at 02:38 AM.
Unfortunately that did not work either. Any other ideas? Would it help if I provided the sheet?
yes...please...
Did the attachment come through?
What I'm needing if their is a difference between week 1 and week2 usage/purchases of more than 3¢ or less than 3¢ is to format a color red.
which cells should be Cond. Formatted?
It would be F19 as compared to D19; G19 as compared to E19; H19 as compared to F19; I19 as compared to g19; J19 as compared to h19; k19 as compared to I19; L19 as compared to J19 and M19 as compared to K19.
Thanks
The original post used D19 - F19. These values are %'s, 12% & 26% respectively, and the difference is: -15%
If you want the conditional format to highlight values not within ±3% band, then wrt. week1 vs. week 2 try:
Also, in C19 you have ¢ Per GuestPlease Login or Register to view this content.
Should that be % per Guest?
No, the row above is %, but row 19 is measuring ¢ per guest. I had it formatted with the ¢, but the boss did not like that format. So, in my first comparison Week1 usage on carving meat ¢ per guest was 12¢ and week2 usage was 26¢ which is > 3¢ so that would need to be formatted red.
Thank you and I hope you can help me.
So, is D19 12 cents (12/100 dollar) or 12/100 of a cent (12/10000 dollar!)? That decimal is confusing if you are actually counting pennies and not dollars.
Yes, it is 12¢ of a dollar. It is simply the usage $$/Meals which is this case usage is $1,390/11,762 meals resulting in 12¢ per guest.
1. I think that using that ¢ character in " ¢ cost per guest" is very misleading, given that the calculations are in dollars, e.g. D16.
2. Since you are doing a division, there may be some roundoff problems.
You can round each value in columns D/F (cost per guest) or just the remainders of the subtractions in the Cond. format formula:
CFF:
Cond.Frmt for entire column D,F:Please Login or Register to view this content.
Applies to:Please Login or Register to view this content.
=$D$19:$D$79,$F$19:$F$79
which could be modified for Wl2:Wk3, Wk3:Wk4, etc.
as the attached.
Please tell me how to download as an attachment so I can open. When I open, it is a "php" format and it will not let me open.
Thank you.
Please tell me how to download as an attachment so I can open. When I open, it is a "php" format and it will not let me open.
Thank you.
To download a file, click on the file you want to download. Then click on Save As, rename from attachment to something more meaningful, put a period after the file name and then type the Excel file extension that is showing the file being downloaded. Then click Open.
I've seen posts by members who use Firefox, Chrome, etc. who have that problem. I use Opera, and have never experienced it so I can't help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks