Excel 2007 : subtracting a number in a cell if another cell is <

[Jettero2112]

Title didn't explain it well. Here's my problem:

I am doing reporting on errors for projects. There are a total number of points available for a project and a total number of actual points for a project.

I need to be able to input a number in the actual points cell and then if the project is late (it would be if another cells number is < a certain amount) have it automatically subtract 10 from the number I input in the actual points cell.

I tried using the conditional formatting but just don't seem to get it. I hope I've explained this reasonably well. Any help would be greatly appreciated.

Thanks.

[scottylad2]

Something along the lines of A1 houses points available, Say 50, B1 is the target date say 01/01/2011 and C1 is the actual Completion date try in D1

=IF(C1="","",IF(C1<B1,A1-10,A1)) which means if the date in C1 is less than B1 you would return 40 but if it was equal or greater it would be 50. If C1 is blank, then D1 would remain blank

hope this helps

[Jettero2112]

The problem is, using your example, that I manually enter an Actual Points number in the cell (D1) that is given to my by the project checker. I would like it to then automatically subtract 10 from the number I input into that cell (D1) if the project is late. I have another cell (call it E1) that tells me how many days before or after the project is bid and use that number to determine if the project is late (E1<4).

I could just do the subtraction manually but just looking to automate as much as I can for others.

Thanks for the input.

[scottylad2]

Don't think it's possible to have D1 house a formula and you input a value. A workaround, would be to house a formula in D1, and have that look at another cell where you can enter the points. For example, you enter the points in A1, and D1 will show that. If the projects on time, it will show the points you input in

[Jettero2112]

I agree that it probably won't work. I've attached a small sample showing what I have. The Earned Points (I3) is where I would manually input the actual points given to my by another person. G3 is where I determine if the project is late or not. <4 days is considered late and I'd want it to auto sub 10 points from the number in I3. I tried using conditional formatting to input a formula but it still doesn't work. It looks like I'll just have to do it manually or figure out macros....

Again, greatly appreciate the help.