Adding calculated value to previous cell

[electricbluelady]

Hi everyone,
I have a spreadsheet numbered from 1-31. This represents the maximum of 31
days in a month. However, all months do not contain 31 days. This is an
example of what I am working with: (day and value columns)

1 100
2 100
3 100
.. 100
.. 100
.. 100
28 100
29 0
30 0
31 0

Total 3100 (fixed constant-no matter if there are 28, 29, 30, or 31 days in
the month).

This example is for February (non-leap year) and the month has 28 days. How
do I calculate the 28th day to equal the total of (3100 - the sum of values
from day 1- day 28?) and appear in the cell for day 28? (leaving the cells
for 29, 30, and 31 blank).

(this would need to work also for days 29, 30, and 31).

 

[Dave O]

Here's one way: if you try this, make sure you have an Add-In called
"Analysis Toolpak" enabled on your version of Excel. (On the menu
click >Tools >Add-Ins and check the Analysis Toolpak box.)

How about a slightly different tack, such as adding up all the days
that are less than or equal to the last day in this month.

I mocked up your data with day numbers in column A and amounts per day
in column B. I derived this formula:
=SUMPRODUCT(--(DAY(EDATE(DATEVALUE("2/1/" & YEAR(NOW())),1)-1)>=$A$1:$A
$30),$B$1:$B$30)

DateValue converts a text representation of a date to an Excel date
value. This could be a cell reference. I've used Year (Now()) to make
this scaleable from year to year.

EDate( date , 1) -1 finds the date of the last day in the relevant
month.

Day derives a day number in the month.

Sumproduct with a unary operator performs a SUMIF- like function to
add days that are less than or equal to the number of days in the
month.

Dave O
Eschew obfuscation

 

[Dave Peterson]

So February 29 would get 300 (29, 30, and 31).
And June 30 would get 200.

I put this in A1:
=DATE(YEAR(TODAY()),1,ROW())

And this in B1:
=Day(a1)

And this in C1:
=IF(MONTH(A1)=MONTH(A1+1),100,3100-100*(DAY(A1)-1))

Then I dragged all three formulas down as far as I needed.

I wasn't sure how your data was laid out, so I dragged it down to row 366.

But if you wanted this stuff on different sheets, you could use this in A1:
=DATE(YEAR(TODAY()),###,ROW())
Replace ### with the month you want for that sheet.

======
Then you could either hide column A

Or convert all the formulas to values by selecting the range
edit|copy
followed by edit|Paste special|Values
and delete column A.

 

[RagDyer]

If the day numbers are in A2 to A32, and the daily values are in B2 to B32,
where do you want the formula that totals the daily values?

You say in the cell for day 28.
Are you talking about A29 or maybe C29?

If you're entering values in Column B, how can you have the value show for
day 28 with the totals for day 1 to day 28?

If you use Column C, and you enter a date in A1, you could enter this
formula in C29 (last day of shortest month):

=IF(A29=32-DAY(A$1-DAY(A$1)+32),3100-SUM($B$2:B29),"")

And copy down to C32 (last day of longest month).

Then, you'll see only *one* row displaying the formula results.