show how much I'm over or under an amount per day for an entire w.

F

flarunner

I'm trying to calculate how much I am either over or under an amount on a
daily basis for an entire week.

I can get different cells to show the individual results, but I can't get
one cell to show the individual results.

I think it might be my math skills and not necessarily Excel.

I would really appreciate any and all help.
 
F

flarunner

I have 'projected' numbers and 'actual' numbers which are summed up at the
end of each week.

Say: 5+5+5+5+5+5=30 for the 'projected' numbers

and: 5+6+6+5+4+5=31 for the 'actual' numbers.

I have a formula which shows the answer to the first, fourth and sixth days
as "0", the second and third days as "1", and the fifth day as "-1".

=IF(OR(actual<>0,projected),actual-projected,)

This shows up in 6 different cells on my spreadsheet.

I would like to be able to have this information in one cell. Right now I
can only get the week sum to show in one cell: "-25" for the first day, "-19"
for the second day, and so on...
 
T

T. Valko

Ok.....

I'm assuming that the projected values are entered all at the same time and
the actual values are entered one at a time. One each day?

So:

B1:G1 = projected
B3:G3 = actual

=IF(COUNT(actual),LOOKUP(10^10,actual)-INDEX(projected,COUNT(actual)),"")

...........B.....C.....D.....E.....F.....G
1........5.....5......5......5.....5......5
2
3................................................

The formula returns blank since no entries have been made in actual.

...........B.....C.....D.....E.....F.....G
1........5.....5......5......5.....5......5
2
3........5......................................

The formula returns 0 (5-5)

...........B.....C.....D.....E.....F.....G
1........5.....5......5......5.....5......5
2
3........5.....6...............................

The formula returns 1 (6-5)

...........B.....C.....D.....E.....F.....G
1........5.....5......5......5.....5......5
2
3........5.....6......7.......................

The formula returns 2 (7-5)

Biff
 
F

flarunner

Exactly what I was looking for! Thank you so very much! What a great help you
have been!

~fla
 
T

T. Valko

You're welcome. Thanks for the feedback!

Biff

flarunner said:
Exactly what I was looking for! Thank you so very much! What a great help
you
have been!

~fla
Click to expand...
 
Top