Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if function (if more that 7 nesting)
End result is to compare prior year numbers to current year to date
sheet 1 has the following columns-- sheet 2 has history-each month totals for a number of years COL A Secured Loan Interest COL B(current month) 39,333.98 COL C(prior month) $36,134.55 COL D(variance MTD) $3,199.43 COL E(current YTD) $113,440.47 COL F(previous YTD) $512,814.15 All of the information on worksheet is filled in with a vlookup formula. I want COL F to add up the totals from sheet 2 for each month up to the current month for this year, so that this year and prior year totals are for the same timeframe. I know that you can only nest 7 if statements in a formula, but I need to do 12 if's to get all the months totaled. Can anyone help with this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if function (if more that 7 nesting)
Assuming dates are in col A, data in col B:
=SUMPRODUCT(--(TEXT('Sheet2'!A2:A100,"yyyy")=TEXT(TODAY(),"yyyy" )-1),--(TEXT('Sheet2'!A2:A100,"mm")<=TEXT(TODAY(),"mm"))) will sum everything from this month and earlier for the previous year. -- Best Regards, Luke M "keflow" wrote in message ... End result is to compare prior year numbers to current year to date sheet 1 has the following columns-- sheet 2 has history-each month totals for a number of years COL A Secured Loan Interest COL B(current month) 39,333.98 COL C(prior month) $36,134.55 COL D(variance MTD) $3,199.43 COL E(current YTD) $113,440.47 COL F(previous YTD) $512,814.15 All of the information on worksheet is filled in with a vlookup formula. I want COL F to add up the totals from sheet 2 for each month up to the current month for this year, so that this year and prior year totals are for the same timeframe. I know that you can only nest 7 if statements in a formula, but I need to do 12 if's to get all the months totaled. Can anyone help with this? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if function (if more that 7 nesting)
Did not work, dates are in col b row 3 and are set as text (Apr-10) this was
done so that the =match formula worked to pick up other info. The data is on rows in each column. =VLOOKUP($A5,History1!$A$1:$CG$176,MATCH('Income Variance'!$B$3,History1!$A$1:$CG$1,0),0) this is the formula in: example-- COL B ROW 3 on the main worksheet. I want COL F to pick up info on the History1 worksheet for all months from 2009 up to and including April if Apr-10 is the column heading at B3. The column headings on the History1 worksheet are also set as text Jan-09 Feb-09 Mar-09, etc. Thanks for any help you can give me on this. -- Thank you, Kathy "Luke M" wrote: Assuming dates are in col A, data in col B: =SUMPRODUCT(--(TEXT('Sheet2'!A2:A100,"yyyy")=TEXT(TODAY(),"yyyy" )-1),--(TEXT('Sheet2'!A2:A100,"mm")<=TEXT(TODAY(),"mm"))) will sum everything from this month and earlier for the previous year. -- Best Regards, Luke M "keflow" wrote in message ... End result is to compare prior year numbers to current year to date sheet 1 has the following columns-- sheet 2 has history-each month totals for a number of years COL A Secured Loan Interest COL B(current month) 39,333.98 COL C(prior month) $36,134.55 COL D(variance MTD) $3,199.43 COL E(current YTD) $113,440.47 COL F(previous YTD) $512,814.15 All of the information on worksheet is filled in with a vlookup formula. I want COL F to add up the totals from sheet 2 for each month up to the current month for this year, so that this year and prior year totals are for the same timeframe. I know that you can only nest 7 if statements in a formula, but I need to do 12 if's to get all the months totaled. Can anyone help with this? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting IF function | Excel Worksheet Functions | |||
Nesting Networkdays function inside and If function | Excel Worksheet Functions | |||
Nesting Sumif function | Excel Worksheet Functions | |||
How do I nesting subtotal function within average function in Exc | Excel Worksheet Functions | |||
Nesting A Function | Excel Worksheet Functions |