Hi,
I am summing up the top 5 values using the following formula:
SUM(LARGE(IF('PBIT-RiskLog'!D1:D1015='PBIT-RiskSummary'!A19,'PBIT-RiskLog'!R1:R1015),{1,2,3,4,5}))
But the array fluctualtes in the 'PBIT-RiskLog' sheet. For some, there are 3 values, some 2, some 4. When i use the above default formula, it gives an error for those which are less than array 5.
Could anyone help me with an IF statement which would allow me to sum up if there are less than 5 values.
Like: IF 2 values then apply the formula with array {1,2}, else if 3 values (1,2,3} etc
I need this quite urgently.
Rgds
John
I am summing up the top 5 values using the following formula:
SUM(LARGE(IF('PBIT-RiskLog'!D1:D1015='PBIT-RiskSummary'!A19,'PBIT-RiskLog'!R1:R1015),{1,2,3,4,5}))
But the array fluctualtes in the 'PBIT-RiskLog' sheet. For some, there are 3 values, some 2, some 4. When i use the above default formula, it gives an error for those which are less than array 5.
Could anyone help me with an IF statement which would allow me to sum up if there are less than 5 values.
Like: IF 2 values then apply the formula with array {1,2}, else if 3 values (1,2,3} etc
I need this quite urgently.
Rgds
John