R
rda
I am looking for a formula that would allow me to calculate a maximum of the
last 4 numeric values in a row divided by their count. For example:
A B C D E F G H I J
1 34 43 30 36 42 44 32
2 82 22 14
3 22 32 44 44 32 12 44 18 22
With the values in the cells displayed I would need a formula to get the
results in the following cells:
4 34 38 35 35 35 35 37 37 38 38
5 82 52 39 39 39 39
6 22 27 32 35 38 33 33 26 24
The results will be used in another worksheet for additional calculations as
needed.
The formula I came up with are as follows:
A4: =A1
B4: =IF(COUNT($A$1:B1)>0,INT(SUM($A$1:B1)/COUNT($A$1:B1)),"")
C4: =IF(COUNT($A$1:C1)>0,INT(SUM($A$1:C1)/COUNT($A$1:C1)),"")
D4: =IF(COUNT($A$11)>0,INT(SUM($A$11)/COUNT($A$11)),"")
This is where I'm stuck. If a value existed in D1 and a value in E1 then
the calculation should not include A1. In this instance there is no value in
either and E4 will have the same result as D4. Once we get to G1 then A1
must be dropped but the remaining need to be summed and divided by 4 with the
results in G4.
I hope I've explained my problem well and someone can come up with a simple
solution.
Thanks,
RDA
last 4 numeric values in a row divided by their count. For example:
A B C D E F G H I J
1 34 43 30 36 42 44 32
2 82 22 14
3 22 32 44 44 32 12 44 18 22
With the values in the cells displayed I would need a formula to get the
results in the following cells:
4 34 38 35 35 35 35 37 37 38 38
5 82 52 39 39 39 39
6 22 27 32 35 38 33 33 26 24
The results will be used in another worksheet for additional calculations as
needed.
The formula I came up with are as follows:
A4: =A1
B4: =IF(COUNT($A$1:B1)>0,INT(SUM($A$1:B1)/COUNT($A$1:B1)),"")
C4: =IF(COUNT($A$1:C1)>0,INT(SUM($A$1:C1)/COUNT($A$1:C1)),"")
D4: =IF(COUNT($A$11)>0,INT(SUM($A$11)/COUNT($A$11)),"")
This is where I'm stuck. If a value existed in D1 and a value in E1 then
the calculation should not include A1. In this instance there is no value in
either and E4 will have the same result as D4. Once we get to G1 then A1
must be dropped but the remaining need to be summed and divided by 4 with the
results in G4.
I hope I've explained my problem well and someone can come up with a simple
solution.
Thanks,
RDA