Compare to cell, range of cell summed and independent cell values

[harcsakutya]

Hi everyone,
(Excel 2013)

As you can see I have to compare the range of cell values (orders) from 2015-08-28 till 38. week with stock value. (Probably I should know more about conitional formatting) If the orders value greater then stock value turn the rest of the cell (time/orders) to red, otherwise fill with green color as available in warehouse.
Is there any idea, how can I solve this issue?

Thanks in advance

I'm not an excel expert, so please explain in detail

Excel file here: https://drive.google.com/file/d/0B9KaCF56CJ3QWV9RUTNPcS1BakU/view?usp=sharing

 

[sijpie]

In your workbook, in Cell G4 open conditional formatting
Select Manage Rules...
Click on New Rule
Click on Use Formula to determine...
put in the following formula to make the cell green if it has an entry:
=G4>0
then set up green cell formatting
click OK
Click on New Rule
Click on Use Formula to determine...
put in the following formula to make the cell red if it has an entry and the sum of it and previous cells are larger than the stock:
=AND(G4>0,SUM($G4:G4)>$D4)
then set up red cell formatting
click OK
Click OK
now you should be backon your sheet.
Click the Format Painter button and paint the whole range (Columns G:O)
Now all the cells with entrys should be either green or red.

 

[harcsakutya]

I think you're in good track, however something wrong in formula (I got notification): =AND(G4>0,SUM($G4:G4)>$D4

Thank you in advance

 

[harcsakutya]

I think you're in good track, however something wrong in formula (I got notification): =AND(G4>0,SUM($G4:G4)>$D4


I try to give more info. that I’d like to solve in (Explanation sheet)
https://drive.google.com/file/d/0B9KaCF56CJ3QaTFTQ0d3TGV1NXc/view?usp=sharing

The brief story is: We have some product in stock in column D. I’ve got table each day in the week to plan all requirements of orders for further days/weeks, that we have to produce to success. So I don’t want to compare it manually and add all of the value rest of cells/days in rows. So I’d like to see how long we are fine with manufacturing source


I think is something like mixed formula.

1. If the value for eg. in D11 greater than or equal (>=) to G11 or G11+H11+I11…….O11, change backround color of each cell to green, which means we have enaugh goods for the rest of the next days

2. If the value in D11 less than (<) G11 or G11+H11 sum, or G11+H11+I11 etc. the cells has to be colored red as long as this sum is true, meaning, not enough goods in warehause compared to value D11.

 

[sijpie]

It works fine with me. i have added the formatting on the middle sheet

https://drive.google.com/file/d/0BxykuY6uKAJ9WFJteFhUcmtJeEU/view?usp=sharing

 

[harcsakutya]

It works fine with me. i have added the formatting on the middle sheet

https://drive.google.com/file/d/0BxykuY6uKAJ9WFJteFhUcmtJeEU/view?usp=sharing

Thank you, the problem was , and ;. Now it's works for me also !

 

[sijpie]

Ah, yes that is a real pain with the various international settings. Something Microsoft has never really anticipated nor addressed properly.

so if you use , as thousand separator and . as decimal separator then the formula is:
=AND(G4>0,SUM($G4:G4)>$D4)

else it is
=AND(G4>0;SUM($G4:G4)>$D4)