Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding calculated value to previous cell
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). -- Thank you all again, Regards! Electricbluelady |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding calculated value to previous cell
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding calculated value to previous cell
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. electricbluelady wrote: 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). -- Thank you all again, Regards! Electricbluelady -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding calculated value to previous cell
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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "electricbluelady" wrote in message ... 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). -- Thank you all again, Regards! Electricbluelady |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Text to Calculated Field | Excel Worksheet Functions | |||
How can I copy a value from a cell and paste it into another cell while adding it to the previous value in that cell | Excel Worksheet Functions | |||
Pivottable adding calculated field? | Charts and Charting in Excel | |||
Adding values from previous worksheets | Excel Worksheet Functions | |||
Pivot table help:calculated field based on previous consecutive va | Excel Discussion (Misc queries) |