Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignore calculation when criteria exceeds 12
I have a worksheet labelled Early Adopter Scheme whereby the cells in column
F contains a start date, column G calculates the number of months between the start date and today(), and column L contain a SUMIF formula, referencing a worksheet labelled OCR (in the same workbook). The data in the OCR worksheet will be updated on a monthly basis. I would like the SUMIF formula in column M to continue performing the calculation where the value in column G (the number of months column) does not exceed 12. When the corresponding cell in column G does exceed 12, i.e. 12 months, the end of the monitoring period is over and we are no longer interested in any additional business Is there any way of doing this? - allowing the calculation to perform until the end of the period? Many thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignore calculation when criteria exceeds 12
Sarah,
Something like this, in cell L2: =IF(G2<13,Old Formula without the leading = sign,"") HTH, Bernie MS Excel MVP "Sarah (OGI)" wrote in message ... I have a worksheet labelled Early Adopter Scheme whereby the cells in column F contains a start date, column G calculates the number of months between the start date and today(), and column L contain a SUMIF formula, referencing a worksheet labelled OCR (in the same workbook). The data in the OCR worksheet will be updated on a monthly basis. I would like the SUMIF formula in column M to continue performing the calculation where the value in column G (the number of months column) does not exceed 12. When the corresponding cell in column G does exceed 12, i.e. 12 months, the end of the monitoring period is over and we are no longer interested in any additional business Is there any way of doing this? - allowing the calculation to perform until the end of the period? Many thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignore calculation when criteria exceeds 12
Try using a simple IF statement in Col M.
=IF(G112,use your SUMIF here,"VOID") -- Cheers, Ryan "Sarah (OGI)" wrote: I have a worksheet labelled Early Adopter Scheme whereby the cells in column F contains a start date, column G calculates the number of months between the start date and today(), and column L contain a SUMIF formula, referencing a worksheet labelled OCR (in the same workbook). The data in the OCR worksheet will be updated on a monthly basis. I would like the SUMIF formula in column M to continue performing the calculation where the value in column G (the number of months column) does not exceed 12. When the corresponding cell in column G does exceed 12, i.e. 12 months, the end of the monitoring period is over and we are no longer interested in any additional business Is there any way of doing this? - allowing the calculation to perform until the end of the period? Many thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignore calculation when criteria exceeds 12
Maybe something like this, indicatively:
=IF(G2<=12,SUMIF(...),"") which will return blanks: "" once col G exceeds 12 -- Max Singapore http://savefile.com/projects/236895 Downloads:20,000 Files:362 Subscribers:62 xdemechanik --- "Sarah (OGI)" wrote: I have a worksheet labelled Early Adopter Scheme whereby the cells in column F contains a start date, column G calculates the number of months between the start date and today(), and column L contain a SUMIF formula, referencing a worksheet labelled OCR (in the same workbook). The data in the OCR worksheet will be updated on a monthly basis. I would like the SUMIF formula in column M to continue performing the calculation where the value in column G (the number of months column) does not exceed 12. When the corresponding cell in column G does exceed 12, i.e. 12 months, the end of the monitoring period is over and we are no longer interested in any additional business Is there any way of doing this? - allowing the calculation to perform until the end of the period? Many thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignore calculation when criteria exceeds 12
Thanks
Is there any way of replacing the "" with the value that was in there previously? For example, if M2 has a value of £10,000 when G2 = 12, can M2 not change when G2 12? I need to retain the last value available, as at the end of the monitoring period. "Bernie Deitrick" wrote: Sarah, Something like this, in cell L2: =IF(G2<13,Old Formula without the leading = sign,"") HTH, Bernie MS Excel MVP "Sarah (OGI)" wrote in message ... I have a worksheet labelled Early Adopter Scheme whereby the cells in column F contains a start date, column G calculates the number of months between the start date and today(), and column L contain a SUMIF formula, referencing a worksheet labelled OCR (in the same workbook). The data in the OCR worksheet will be updated on a monthly basis. I would like the SUMIF formula in column M to continue performing the calculation where the value in column G (the number of months column) does not exceed 12. When the corresponding cell in column G does exceed 12, i.e. 12 months, the end of the monitoring period is over and we are no longer interested in any additional business Is there any way of doing this? - allowing the calculation to perform until the end of the period? Many thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignore calculation when criteria exceeds 12
Sarah,
Well, it depends on how your calculation depended on the value 12: say you have a formula like =1000/G2 Then use =IF(G2<13,1000/G2,1000/12) Of course, your might be able to replace all instances of G2 in your formula with MIN(G2/12) and not use the IF function at all: =1000/MIN(G2,12) will return the same as the formula above.... HTH, Bernie MS Excel MVP "Sarah (OGI)" wrote in message ... Thanks Is there any way of replacing the "" with the value that was in there previously? For example, if M2 has a value of £10,000 when G2 = 12, can M2 not change when G2 12? I need to retain the last value available, as at the end of the monitoring period. "Bernie Deitrick" wrote: Sarah, Something like this, in cell L2: =IF(G2<13,Old Formula without the leading = sign,"") HTH, Bernie MS Excel MVP "Sarah (OGI)" wrote in message ... I have a worksheet labelled Early Adopter Scheme whereby the cells in column F contains a start date, column G calculates the number of months between the start date and today(), and column L contain a SUMIF formula, referencing a worksheet labelled OCR (in the same workbook). The data in the OCR worksheet will be updated on a monthly basis. I would like the SUMIF formula in column M to continue performing the calculation where the value in column G (the number of months column) does not exceed 12. When the corresponding cell in column G does exceed 12, i.e. 12 months, the end of the monitoring period is over and we are no longer interested in any additional business Is there any way of doing this? - allowing the calculation to perform until the end of the period? Many thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ignore calculation if text | Excel Worksheet Functions | |||
Median calculation and ignore zeros | Excel Discussion (Misc queries) | |||
calculation to ignore a 0 (zero) value | Excel Worksheet Functions | |||
Ignore errors when calculation average of multiple ranges | Excel Worksheet Functions | |||
Can a calculation ignore text if it occurs in formula's cell range | Excel Worksheet Functions |