Hi there,
I am trying to change the formatting (color) of a number of cells in an array (4 cells in a row) given a value that I am going to input in 1 of those cells. This input will be compared to an array of values already input. So depending on the value of the cell I am inputting, if it is lesser than my base I want it to be compared to, the row will become green. Or stay white.
Clearly, I have an array o different times (number). It's a multidimensional array, depending on the total distance of the event (100m, 200m, 400m or 800m) and their split by repetitions of 25m, 50m, 75m or 100m.
When I hand-enter the time performed during the day, I ask for the average time in the voerall set.
Let's say I have someone's split of the 100m by 25m is 25 sec.
On a given day, I will give a set of let's say 8x25m.
At the end, I will input the athlete's average achieved time. If it is 25sec or faster (24, 23...), I want the row ( day, set, pace time...) to become green. Otherwise it stays white.
This is the rule formula I was thinking about, but can't find better.
=IF($C10+$D10=125, $AF$5 (cell to be compared to), IF($C10+$D10=150, $AG$5, IF($C10+$D10=225, $AF$6, IF($C10+$D10=250, $AG$6, IF($C10+$D10=275, $AH$6, FALSE)))))
I have Tried also something like
=OR(AND($C10+$D10=125, $H10 (input cell) <=H$5 (time of reference for the distance/rep), AND($C10+$D10=150, $H10<=I$5), AND($C10+$D10=225, $H10<=H$6), AND($C10+$D10=250, $H10<=I$6), AND($C10+$D10=275, $H10<=J$6))
And, it does not work. It doesn't adapt dynamically.
Does it make sense?
Any thoughts?
Thank you.
Hi there,
I am trying to change the formatting (color) of a number of cells in an array (4 cells in a row) given a value that I am going to input in 1 of those cells. This input will be compared to an array of values already input. So depending on the value of the cell I am inputting, if it is lesser than my base I want it to be compared to, the row will become green. Or stay white.
Clearly, I have an array o different times (number). It's a multidimensional array, depending on the total distance of the event (100m, 200m, 400m or 800m) and their split by repetitions of 25m, 50m, 75m or 100m.
When I hand-enter the time performed during the day, I ask for the average time in the voerall set.
Let's say I have someone's split of the 100m by 25m is 25 sec.
On a given day, I will give a set of let's say 8x25m.
At the end, I will input the athlete's average achieved time. If it is 25sec or faster (24, 23...), I want the row ( day, set, pace time...) to become green. Otherwise it stays white.
This is the rule formula I was thinking about, but can't find better.
=IF($C10+$D10=125, $AF$5 (cell to be compared to), IF($C10+$D10=150, $AG$5, IF($C10+$D10=225, $AF$6, IF($C10+$D10=250, $AG$6, IF($C10+$D10=275, $AH$6, FALSE)))))
I have Tried also something like
=OR(AND($C10+$D10=125, $H10 (input cell) <=H$5 (time of reference for the distance/rep), AND($C10+$D10=150, $H10<=I$5), AND($C10+$D10=225, $H10<=H$6), AND($C10+$D10=250, $H10<=I$6), AND($C10+$D10=275, $H10<=J$6))
And, it does not work. It doesn't adapt dynamically.
Does it make sense?
Any thoughts?
Thank you.