Counting actuals that have exceeded or missed a target by percentages

[SPEARSON1234]

I have a two set of numbers, a target and an actual and I have a percentage formula against them, working what percentage the actual has met of not met the target by. I now want to add a column that counts how many have exceeded the target by more than 10%, exceeded less than 10%, missed by less than 10% and missed by more than 10%. It will also need to account for the target and or the actual being zero.

Any help very gratefully received.

Thanks in advance

 

[etaf]

It will also need to account for the target and or the actual being zero.

????

and I have a percentage formula against them,

What column
Countif( Percent column , ">"&0.1)
CountifS( Percent column , "<="&0.1,Percent column , ">="&0)

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[Peter_SSs]

My suggestion is basically the same as @etaf's but you don't have to have the symbol and number separate and concatenated by "&". You can just put the number in the text string with the inequality sign(s)

Is this what you are after?

24 12 17.xlsm
	A	B	C	D	E	F	G	H
1	Target	Actual	%	Exceeded > 10%	Exceeded <= 10%	Met Target Exactly	Missed <= 10%	Missed > 10%
2	188	200	6.38%	8	1	2	2	6
3	623	740	18.78%
4	512	214	-58.20%
5	502	243	-51.59%
6	918	918	0.00%
7	786	780	-0.76%
8	200	286	43.00%
9	398	204	-48.74%
10	811	502	-38.10%
11	534	950	77.90%
12	730	728	-0.27%
13	630	993	57.62%
14	979	167	-82.94%
15	966	966	0.00%
16	789	651	-17.49%
17	667	796	19.34%
18	100	323	223.00%
19	556	851	53.06%
20	748	850	13.64%
Target

Cell Formulas
Range	Formula
D2	D2	=COUNTIF(C2:C20,">0.1")
E2	E2	=COUNTIFS(C2:C20,"<=0.1",C2:C20,">0")
F2	F2	=COUNTIF(C2:C20,0)
G2	G2	=COUNTIFS(C2:C20,">=-0.1",C2:C20,"<0")
H2	H2	=COUNTIF(C2:C20,"<-0.1")
C2:C20	C2	=(B2-A2)/A2

 

[etaf]

@Peter_SSs​

FYI
I have found on some occasions , when answering questions the OP has found , using ">0" has not worked , or the question is OP using some form of ">0" and its not working , and when I suggested using ">"&0 it has worked , this has been on answering questions on forums , not sure why , and so i do tend to use & on all.

 

[Peter_SSs]

on some occasions , ... found , using ">0" has not worked

Fair enough, but I have not experienced or seen that.

 

[SPEARSON1234]

Thank you both for your help, has worked perfectly very much appreciated