Above or Below Target

[Dans Issues]

Hi All,

I was hoping that some one could help with a formular.
Situation:
Each month a delivery target is set, this information is in the same work book but in a different tab.
Question one
How can I write a formula that doesn't show a negative if the target is over delivered (='Raw Delivery Data'!B46-D4) i.e. Target 1000 and delivered Equal to or grater than?. If under delivered the formula needs to be a negative red i.e. Target 1000 Delivered 900? I think the formula I'm writing is incorrect

Question Two:

In the percentage Loss or Gain how can I illustrate a percentage? ie... 40% under target (red) or 40% above target?

I'm a First time user on this site and I'm using excel more and more each day so I think I'm going to become a regular here
Thank you for your help

A month	B Target	C Accrual Delivery	D Plus or minus	E Percentage loss or gain (%)
January	3000	1100	?	?
February	2100	3330	?	?
March	2230	1100	?	?
April	8802	1100	?	?
May	3330	1100	?	?
June	2210	4450	?	?
July	3654	1100	?	?
August	2541	1100	?	?
September	2569	1100	?	?
October	6528	8809	?	?
November	1245	1100	?	?
December	3687	1100	?	?

 

[etaf]

C2-B2

will give a - for under delivered and positive for over delivered
is that the results you expected

you can the use conditional formatting for colour quite easy - but i can explain that - if the below is correct

the percent - is that the amount missing / target
ie target 100 delivered 25

so missing 75

so either -75% = missing / target = 75/100
or delivered 25

25/100. = 25%

Book5
	A	B	C	D	E
1	A month	B Target	C Accrual Delivery	D Plus or minus	E Percentage loss or gain (%)
2	January	3000	1100	-1900	63%
3	February	2100	3330	1230	59%
4	March	2230	1100	-1130	51%
5	April	8802	1100	-7702	88%
6	May	3330	1100	-2230	67%
7	June	2210	4450	2240	101%
8	July	3654	1100	-2554	70%
9	August	2541	1100	-1441	57%
10	September	2569	1100	-1469	57%
11	October	6528	8809	2281	35%
12	November	1245	1100	-145	12%
13	December	3687	1100	-2587	70%
Sheet1

Cell Formulas
Range	Formula
D2:D13	D2	=C2-B2
E2:E13	E2	=ABS(D2/B2)

 

[Dans Issues]

Thanks for replying so quickly ETAF,

So how can I use conditional formatting to highlight in Red the negatives (Below delivery target) in column D and the below delivery target percentages in column E?

Thanks for the help

 

[etaf]

If my example is the % OK,

as i did ask for clarification

the percent - is that the amount missing / target
ie target 100 delivered 25

so missing 75

so either -75% = missing / target = 75/100
or delivered 25

25/100. = 25%

what is the correct % you want to show ?


I have changed the % to show the + or - , then you can see the out of target %
as its the difference in target

then anything in column D that is negative ie below 0 will need formatting
then anything in column E that is negative ie below 0% will need formatting

you need 2 rules , 1 for D and 1 for E for different colours
But you could apply just 1 rule if you want the same colour for both - as a negative in D will also include a % that needs highlighting


Select the range in D, you want to format - say D2 to D100

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
D2:D100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=D2 < 0

Format [Number, Font, Border, Fill]. Fill to the colour you want
choose the format you would like to apply when the condition is true
OK >> OK

Now add another rule
Select the range for E - say E2:E100
Formula
=E2 < 0
apply the format
2 rules

Book5
	A	B	C	D	E
1	A month	B Target	C Accrual Delivery	D Plus or minus	E Percentage loss or gain (%)
2	January	3000	1100	-1900	-63%
3	February	2100	3330	1230	59%
4	March	2230	1100	-1130	-51%
5	April	8802	1100	-7702	-88%
6	May	3330	1100	-2230	-67%
7	June	2210	4450	2240	101%
8	July	3654	1100	-2554	-70%
9	August	2541	1100	-1441	-57%
10	September	2569	1100	-1469	-57%
11	October	6528	8809	2281	35%
12	November	1245	1100	-145	-12%
13	December	3687	1100	-2587	-70%
Sheet1

Cell Formulas
Range	Formula
D2:D13	D2	=C2-B2
E2:E13	E2	=(D2/B2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E13	Expression	=E2<0	text	NO
D2:D13	Expression	=D2<0	text	NO

JUST 1 RULE same colour
you could also go back to showing positive % for the error if needed
BUT check the % is what you want and expected

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
D2:E100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=$D2 > 0

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

Book5
	A	B	C	D	E	F
1	A month	B Target	C Accrual Delivery	D Plus or minus	E Percentage loss or gain (%)
2	January	3000	1100	-1900	-63%
3	February	2100	3330	1230	59%
4	March	2230	1100	-1130	-51%
5	April	8802	1100	-7702	-88%
6	May	3330	1100	-2230	-67%
7	June	2210	4450	2240	101%
8	July	3654	1100	-2554	-70%
9	August	2541	1100	-1441	-57%
10	September	2569	1100	-1469	-57%
11	October	6528	8809	2281	35%
12	November	1245	1100	-145	-12%
13	December	3687	1100	-2587	-70%
Sheet1

Cell Formulas
Range	Formula
D2:D13	D2	=C2-B2
E2:E13	E2	=(D2/B2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:E13	Expression	=$D2<0	text	NO

This is using 1 colour conditional formatting the 2 columns based on a positive r negative in D
and using an ABS() again for the % so it stays positive

Book5
	A	B	C	D	E	F
1	A month	B Target	C Accrual Delivery	D Plus or minus	E Percentage loss or gain (%)
2	January	3000	1100	-1900	63%
3	February	2100	3330	1230	59%
4	March	2230	1100	-1130	51%
5	April	8802	1100	-7702	88%
6	May	3330	1100	-2230	67%
7	June	2210	4450	2240	101%
8	July	3654	1100	-2554	70%
9	August	2541	1100	-1441	57%
10	September	2569	1100	-1469	57%
11	October	6528	8809	2281	35%
12	November	1245	1100	-145	12%
13	December	3687	1100	-2587	70%
Sheet1

Cell Formulas
Range	Formula
D2:D13	D2	=C2-B2
E2:E13	E2	=ABS(D2/B2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:E13	Expression	=$D2<0	text	NO

 

[Dans Issues]

Hi ETAF,

The correct percentage I was wanting to show was all deliveries above target. You have illustrated this in all of your examples.

Thank you for you time and experience, I'm sure I will hear from you again when I have other issues.

Regards

"Dan's Issues"

 

[etaf]

you are welcome