# Office 365 >  >  formula index shows correct answer but cell reports #VALUE and or #NUM

## CdnFillie

Can anyone help me with this.. below is the formula I use to get the 3 lowest scores for team points.  

=IF((V28:V33)>1,SUM(SMALL(V28:V33,{1,2,3,4,5,6})),0)

Team # 3	Gross	HDCP	Net	NHDCP

Golfer 1                41	7	34	5
Golfer 2    	0	10	0	10
Golfer 3   	0	13	0	13
Golfer 4   	51	13	38	15
SPARE		51	14	37
SPARE		0	0	0

       Total		          #VALUE!	

This works for any team that has three reg golfer score..it becomes a problem when a spare is used ... can someone help before I lose my head on this thanks in advance

----------


## :) Sixthsense :)

Not sure V28:V33 refers to which data and at the same time IT IS AN ARRAY FORMULA which needs to be entered with *CTRL+SHIFT+ENTER* combination.

Please attach a sample workbook with enough data to make it clear what is needed.  Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.  Make sure your desired results are demonstrated, mock them up manually if needed.  Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

_Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.)._

----------


## CdnFillie

Gross	HDCP	Net	NHDCP

39	5	34	5
47	12	35	12
56	12	44	12
48	16	32	16
		0	
		0	
Total		101	
Points			

D=column Net row 6 to 11

The formula in D6 to D11 is =IF(B6-C6<0,"0",B6-C6)

=IF(D6:D11)>1,SUM(SMALL(D6:D11,{1,2,3,4,5})),0)

This will give me the correct answer of the sum of the three lowest scores of the 6 cells omitting the 0 scores

the problem I'm having is when there is a zero score in any two cells of row 6, 7, 8 or 9 that formula gives me #NUM error
	Gross	HDCP	Net	NHDCP

	41	7	34	5
	0	10	0	10
	0	13	0	1
	51	13	38	15
SPARE	51	14	37	
SPARE			0	
	Total		109	
	Points			

If there is a need to add one Spare score to the other two I have to change the formula to 

=IF(SUM(V28:V33)<>0,SUM(SMALL(V28:V33,{1,2,3,4})),0)

But this formula does not work in the above example

              Gross	HDCP	NET	NHDCP

	0	13	0	13

	45	8	37	9
	0	4	0	4
	0	12	0	12
SPARE	51	14	37	
SPARE	51	14	37	
	Total		111	
	Points			


If I have to add two spares I have to change the formula to this

=IF(SUM(V72:V77)>1,SUM(SMALL(V72:V77,{1,2,3})),0)

Either way it doesn't want to consider the value in row 10 and 11 if it involves counting a spare score

I hope I'm explaining this correctly..thanks for your help

----------

