I have been stuck for months

mikeeakins

New Member
Joined
Mar 29, 2023
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hello! I asked this question and almost worked so I believe a little more tweaking is gonna be involved. My question was ...
I need to conditional format a series of cells (M9:AK9) the cell that I am comparing to is (E9)

This was my result for formatting below the example shown below the square it should be formatted (green, green, yellow).
One this I forgot to mention was that as I increase or decrease cell E9, the color format should change also to update the formatted row. As E9 changes the green, yellow and red formatting should change in the row.
I have 50 rows of data that I will be using this on in the same spread sheet.


1. Select the cells M9:AK9. 2. Go to the Home tab and click on Conditional Formatting. 3. Select "New Rule" and then choose "Use a formula to determine which cells to format." 4. In the "Format values where this formula is true" field, enter the following formula for green fill: =(M9+N9)>E9*(1+0.6) 5. Click on the Format button and choose the fill color you want for the green cells. 6. Click OK to close the Format Cells dialog box. 7. Click OK to apply the formatting rule. 8. Repeat steps 3-7 for yellow fill. In the "Format values where this formula is true" field, enter the following formula: =(M9+N9+O9)>E9*(1+0.6) 9. Click on the Format button and choose the fill color you want for the yellow cells. 10. Click OK to close the Format Cells dialog box. 11. Click OK to apply the formatting rule. 12. Repeat steps 3-7 for red fill. In the "Format values where this formula is true" field, enter the following formula: =OR(SUM(M9:N9)>E9,SUM(M9:O9)>E9*1.6) 13. Click on the Format button and choose the fill color you want for the red cells. 14. Click OK to close the Format Cells dialog box. 15. Click OK to apply the formatting rule. Now, the cells M9:AK9 will be formatted with green, yellow, and red colors based on the criteria you provided. Kindly let me know.
I appreciate any help I can get for this problem.
Thanks,

Mike
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Hi & welcome to MrExcel.
You haven't actually said what is wrong, but you need to lock the reference cell like
Excel Formula:
=(M9+N9)>$E9*(1+0.6)
 
Upvote 0
Hi & welcome to MrExcel.
You haven't actually said what is wrong, but you need to lock the reference cell like
Excel Formula:
=(M9+N9)>$E9*(1+0.6)
ok so,
I want to have e9 as my inventory amount, example 3500
so m9+n9 equals 1000 so its green
m9+n9+o9=2000 so still green
m9+n9+o9+p9=2900 so this total is within 60% so its yellow
m9+n9+o9+p9+q9=4000 so its red.
As E9 inventory fluctuates the green, yellow, and red cells should change to reflect the inventory.
My spreadsheet is pretty large so I will have 30 rows of data just like e9.
 
Upvote 0
Ok, how about, for green
Excel Formula:
=SUM($M9:N9)<$E9*1.6
 
Upvote 0
Ok, how about, for green
Excel Formula:
=SUM($M9:N9)<$E9*1.6
I think thats close .
It worked when the inventor was 3309 (e9), and green was to (O9 4/1) which added to 2960 so it was correct the rest of the way.
When I changed the inventory to 4000 it was green to (T9 4/6) but that total was 7110 and it should have been yellow at (Q9 4/3) with that total was 4260.
1680119345349.png
 

Attachments

  • 1680119292242.png
    1680119292242.png
    2.7 KB · Views: 24
Upvote 0
I think thats close .
It worked when the inventor was 3309 (e9), and green was to (O9 4/1) which added to 2960 so it was correct the rest of the way.
When I changed the inventory to 4000 it was green to (T9 4/6) but that total was 7110 and it should have been yellow at (Q9 4/3) with that total was 4260.
View attachment 88653
This formula will be in every row
1680120427751.png
 
Upvote 0
Ok, how about
Excel Formula:
=SUM($M9:N9)<$E9*0.6
 
Upvote 0
So what would you use for yellow and red?
Yellow seems to be =SUM($M9:N9)>=$E9*60%
 
Upvote 0
I would have yellow should be
Excel Formula:
=SUM($M9:N9)<$E9*1.6
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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