Hi
I’m hoping someone can help me.
I’m trying to reduce the number of columns needed to tally up the totals of 3 possible outcomes
I have 8 Columns; Column A= Item Code; B= Category; C=Target figure; D=Actual figure; E=Outcome Code; F=Items scoring 0; G=Items scoring 1; H=Items scoring 2
I want to be able to filter categories in Column B.
Column C contains target figures. Each is different.
Column D contains the actual total.
Column E – If the actual total is greater than the target figure the figure 2 appears here
If the actual total is less than 90% of the target figure the figure 0 appears here.
If the actual total is 90% or more but less than 100% of the target figure the figure 1 appears here.
Column F – If there is a 0 in Column E, a 1 appears here
=if (E5=”0”, “1”,””)
Column G – If there is a 1 in Column E, a 1 appears here
=if (E5=”1”, “1”,””)
Column H – If there is a 2 in Column E, a 1 appears here
=if (E5=”2”, “1”,””)
At present I have these formulas at the top of F, G and H respectively
=subtotal (2, F5:F19)
= subtotal (2, G5:F19)
= subtotal (2, H5:F19)
However I want to avoid the need for Columns G and H if possible as they make the spreadsheet look clumsy and untidy. Can you suggest a way of cutting down on these 2 columns but still giving me the same results for items scoring 0, 1 and 2?
Thanks in advance
Dean
I’m hoping someone can help me.
I’m trying to reduce the number of columns needed to tally up the totals of 3 possible outcomes
I have 8 Columns; Column A= Item Code; B= Category; C=Target figure; D=Actual figure; E=Outcome Code; F=Items scoring 0; G=Items scoring 1; H=Items scoring 2
I want to be able to filter categories in Column B.
Column C contains target figures. Each is different.
Column D contains the actual total.
Column E – If the actual total is greater than the target figure the figure 2 appears here
If the actual total is less than 90% of the target figure the figure 0 appears here.
If the actual total is 90% or more but less than 100% of the target figure the figure 1 appears here.
Column F – If there is a 0 in Column E, a 1 appears here
=if (E5=”0”, “1”,””)
Column G – If there is a 1 in Column E, a 1 appears here
=if (E5=”1”, “1”,””)
Column H – If there is a 2 in Column E, a 1 appears here
=if (E5=”2”, “1”,””)
At present I have these formulas at the top of F, G and H respectively
=subtotal (2, F5:F19)
= subtotal (2, G5:F19)
= subtotal (2, H5:F19)
However I want to avoid the need for Columns G and H if possible as they make the spreadsheet look clumsy and untidy. Can you suggest a way of cutting down on these 2 columns but still giving me the same results for items scoring 0, 1 and 2?
Thanks in advance
Dean