Execute a Function Only Once when a Condition First Occurs

[BobH]

Once again, I come happily in your midst to share my ignorance without embarrassment.

There might be - probably is - a way built into Excel to handle this situation. If there is I don't know of it and would very much appreciate being enlightened. If there is not and you have a suggestion for solving the problem, I'd also be much obliged.

I have a spreadsheet in which I want to check a cumulative variable and when it reaches a specific value, execute a formula BUT do this only once. Thereafter, when the value has been exceeded, I want to execute a different formula. Half a century ago when I was a young COBOL programmer (OK, now I've outed myself; could it possibly have been so long ago?), I would have declared a switch and set its value when the threshold value was reached and tested it before executing the 'different formula' referenced above. I suppose I could do something similar, but thought I'd ask before worrying my old grey cells.

[HansV]

Could you provide more detailed, specific information?

[BobH]

Hi Hans!

I've attached a copy of the worksheet. Notice col. H, row 29. Cumulative KWH exceeds 1000 at that point. When that happens, I get a $100 credit that I apply to cumulative charges, but I only want to apply that credit once. The IF statement I have defined in col. M is copied down the column therefore the credit repeats.

Does that make sense?

Copy of Copy of DailyData-4.xls

[HansV]

In M7:

=$M6+$L7-100*AND(H6<=1000,H7>1000)

Fill down. This subtracts 100 if the previous cumulative value is 1000 or less, and the current cumulative value is over 1000.

[BobH]

Thank you, once again, Mr. V!!!

I caught myself up in a singular view of the problem and couldn't break free. That sound you just heard was me slapping myself in the forehead.