How to code

[mallard]

Hi,
I want to achieve the following,
if B2 > C2 then G2 = 1 + G2 else G3 = 1 + G3
if B2 = B3 then G2 = 0.5 + G2 and G3 = 0.5 + G3

Is this type of thing possible?

thanks for looking.

[Lupp]

Spreadsheets a basically designed to achieve results based on formulae. In addition a process to decide which "dirty cells" need recalculation in what order is next to the heart of spreadsheet software.

Concerning your question there are two fundamental facts resulting in a "No!" with a whispered "except" probably.

-1- Formulae return their results to the cells they are contained in. They cannot write to other cells.
-2- Spreadsheets are not history-aware. Having calculated something meaning "previos value plus 1" the resulting value would be the previous value when the next recalculation is triggered by the mentioned automatisms.

Trying to circumvent the resultung restrictions would require tricky and probably unreliable means - even if you consider to do it by "user code" in the sense of writing an additional piece of program applied inside your spreadsheet document. Basically you need to override the built-in processes triggering recalculation, and to establish a substitute you have full controll over. The least problematic might be to rely on the "Content changed" event or to register a respective listener. But you won't get a history this way again, and would therefore not be able to create a kind of "undo" for cases of inadvertent editing...

[mallard]

Thanks Lupp for the explanation. I'll use a couple of sumif's and get round it another way.