Ignore calculation when criteria exceeds 12

[Sarah (OGI)]

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

 

[Bernie Deitrick]

Sarah,

Something like this, in cell L2:

=IF(G2<13,Old Formula without the leading = sign,"")

HTH,
Bernie
MS Excel MVP

 

[RyanH]

Try using a simple IF statement in Col M.

=IF(G1>12,use your SUMIF here,"VOID")

 

[Max]

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)]

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]

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