Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for calculating YTD values
Hi
I am trying to set up a table that, when you change the date every month for a range of values by month it sums up the YTD value. eg Jan Feb Mar April May June Salaries 10000 12000 18000 18000 20000 20000 Annual Leave 6000 5000 5000 4500 5500 6000 for example , If we are in the month of April, if I enter the date April the formula should calculate Jan - April Salaries and annual leave? And if I change the date to June the formula should calculate value from Jan to June. Hope it makes sense Thanks -- SC |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for calculating YTD values
Assume the table you posted is in A1:G3
Assume the "month" will be input in A5, eg: Mar You could place this in A6: =SUMPRODUCT(--(OFFSET(B2,,,,MATCH($A$5,$B$1:$IV$1,0)))) to derive the salaries summation from Jan to Mar. Copy A6 down to A7 to derive the similar summation for "Annual leave" Reconfigure the above to suit your desired layout -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Suan" wrote: Hi I am trying to set up a table that, when you change the date every month for a range of values by month it sums up the YTD value. eg Jan Feb Mar April May June Salaries 10000 12000 18000 18000 20000 20000 Annual Leave 6000 5000 5000 4500 5500 6000 for example , If we are in the month of April, if I enter the date April the formula should calculate Jan - April Salaries and annual leave? And if I change the date to June the formula should calculate value from Jan to June. Hope it makes sense Thanks -- SC |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for calculating YTD values
With your month name column headers in the range B1:M1
A10 = some month name =SUM(B2:INDEX(B2:M2,MATCH(A10,B1:M1,0))) -- Biff Microsoft Excel MVP "Suan" wrote in message ... Hi I am trying to set up a table that, when you change the date every month for a range of values by month it sums up the YTD value. eg Jan Feb Mar April May June Salaries 10000 12000 18000 18000 20000 20000 Annual Leave 6000 5000 5000 4500 5500 6000 for example , If we are in the month of April, if I enter the date April the formula should calculate Jan - April Salaries and annual leave? And if I change the date to June the formula should calculate value from Jan to June. Hope it makes sense Thanks -- SC |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for calculating YTD values
"Suan" wrote:
I am trying to set up a table that, when you change the date every month for a range of values by month it sums up the YTD value. [....] If we are in the month of April, if I enter the date April the formula should calculate Jan - April Salaries and annual leave? And if I change the date to June the formula should calculate value from Jan to June. Those sound like two very different problems to me. I wonder if you are over-specifying your requirements in the latter, resulting in suggested solutions that are more complicated than you need. Do you really need to an arbitrary month through which to sum? For example, if you have data through June, would you really ask for the sum through April or any other month earlier than June? Or do you really mean that you want the sum through the last month for which you have data? And as you enter data for each later month, you want the sum to automatically include the later months? For the latter, if salaries are in B2:M2, the YTD total is simply =SUM(B2:M2). Similarly, if annual leave amounts are in B3:M3, the YTD total is =SUM(B3:M3). Alternatively, if you might want a row ("Salaries YTD") that gives the YTD amount for each month for which you have data. Starting in B4, for example, and copying across through M4, enter: =if(B2="","",SUM($B$2:B2)) As you copy that cell across, the formula becomes, in B3 for example: =if(B3="","",SUM($B$2:B3)) Similar for "Annual leave YTD, use C2 and SUM($C$2:C2) to begin with. ----- original message ----- "Suan" wrote in message ... Hi I am trying to set up a table that, when you change the date every month for a range of values by month it sums up the YTD value. eg Jan Feb Mar April May June Salaries 10000 12000 18000 18000 20000 20000 Annual Leave 6000 5000 5000 4500 5500 6000 for example , If we are in the month of April, if I enter the date April the formula should calculate Jan - April Salaries and annual leave? And if I change the date to June the formula should calculate value from Jan to June. Hope it makes sense Thanks -- SC |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating different values | Excel Discussion (Misc queries) | |||
CALCULATING A FORMULA TO USE A SPECIFIC CELL'S VALUES | Excel Worksheet Functions | |||
calculating matrix values | Excel Worksheet Functions | |||
How do I average a formula without calculating zero values? | Excel Discussion (Misc queries) | |||
Pbl calculating US$ values | Excel Discussion (Misc queries) |