Hi
trying to set up a spreadsheet to record High/Low of both players or teams, I have been using the If function but it just overwrites, ie if low is 1.1 , it will overwrite if it goes back to 1.15, i want it to only overwrite if if goes lower on that side.
Anyone help please.
Alan
high low in 2 way sports markets
You could use the min and max functions but I always find they often fail (I suspect something to do with the sheet updating live), or use vba but again, I try to avoid using vba as if things change or you need to alter something, its too arcane and cumbersome to adapt quickly.
I have always found circular references to be much more reliable way to record max and min’s , plus, its quite straight forward to alter a sheet on the fly, without having to think too much.
enable circular references - tools – options – calculation tab, tick the box that says iteration, in the box that says “maximum iterations” type 1.
Assuming the cell where you wish to record the minimum value reached is b2 and the cell where the value you wish to record the minimum value reached is being displayed/updated is a2 :-
Type the following in cell b2
=IF($K$1,0,IF(ISERROR(B2),B2,IF(B2=0,A2,IF(A2>B2,B2,A2))))
the minimum value reached in cell a2 will be displayed dynamically in cell b2
cell k1 (or whatever cell you happen choose) is important has it resets the formula; if you don’t reset when you change market, it will display the minimum value recorded from the previous market unless of cause if a new value falls lower, making it useless/pointless..
when you change to a new market in bet angle , type 1 into cell k1, press enter. Then type 0 into k1,press enter,and the cell b2 will (should) record from the current value in cell a2, capturing the minimum value reached in cell a2.
The only other way to reset it would be disconnect, close the work, open the workbook, connect, far too much faffing around; or you could record a macro to create a nice little button , that will reset the sheet (I could not be asked to do that, again far too much faffing around, simple to type 1 and zero as far as I am concerned).
To record the max value reached more or the less the same, slight difference.
Assuming the cell where you wish to record the maximum value reached is c2 and the cell where the value you wish to record the maximum value reached is being displayed/updated is a2 :-
=IF($K$1,0,IF(ISERROR(C2),A2,IF(C2>A2,C2,A2)))
the maximum value reached in cell a2 will be displayed dynamically in cell c2
cell k1 as above blah, blah, blah.
I have always found circular references to be much more reliable way to record max and min’s , plus, its quite straight forward to alter a sheet on the fly, without having to think too much.
enable circular references - tools – options – calculation tab, tick the box that says iteration, in the box that says “maximum iterations” type 1.
Assuming the cell where you wish to record the minimum value reached is b2 and the cell where the value you wish to record the minimum value reached is being displayed/updated is a2 :-
Type the following in cell b2
=IF($K$1,0,IF(ISERROR(B2),B2,IF(B2=0,A2,IF(A2>B2,B2,A2))))
the minimum value reached in cell a2 will be displayed dynamically in cell b2
cell k1 (or whatever cell you happen choose) is important has it resets the formula; if you don’t reset when you change market, it will display the minimum value recorded from the previous market unless of cause if a new value falls lower, making it useless/pointless..
when you change to a new market in bet angle , type 1 into cell k1, press enter. Then type 0 into k1,press enter,and the cell b2 will (should) record from the current value in cell a2, capturing the minimum value reached in cell a2.
The only other way to reset it would be disconnect, close the work, open the workbook, connect, far too much faffing around; or you could record a macro to create a nice little button , that will reset the sheet (I could not be asked to do that, again far too much faffing around, simple to type 1 and zero as far as I am concerned).
To record the max value reached more or the less the same, slight difference.
Assuming the cell where you wish to record the maximum value reached is c2 and the cell where the value you wish to record the maximum value reached is being displayed/updated is a2 :-
=IF($K$1,0,IF(ISERROR(C2),A2,IF(C2>A2,C2,A2)))
the maximum value reached in cell a2 will be displayed dynamically in cell c2
cell k1 as above blah, blah, blah.
