Formula help

[VbaHell]

Can I push my luck please and ask another question

In column A1:A5 I have numeric values with a total, this is a set target

In column B1:B5 I have numeric values that I enter in as a score

I then divide total of column B in A for the actual percentage score

If I enter N/A into any of the B1:B5 cells I would like the Target reduced in the opposite cell in A1:A5

Example1

A B
2 1
2 2
2 1
1 1
2 2

9 7 = 77.8% totals

Example2

A B
2 1
2 n/a
2 1
1 1
2 2

7 5 = 71.4% totals

maybe a sumif type statement in the total sum saying not to add value in A1 if B1 has N/A and so on

 

[godsaaint]

Try (format the cell as %):

=SUM(B1:B5)/SUMIF(B1:B5,"<>"&"N/A",A1:A5)

 

[AliGW]

You might consider the AVERAGEIF function.

 

[Tetra201]

Is this what you need?

=SUM(B1:B5)/SUMPRODUCT(--ISNUMBER(A1:A5),--ISNUMBER(B1:B5),A1:A5)

 

[Snakehips]

Another variant..

Excel Workbook
	A	B	C
1	2	1	*
2	2	N/A	*
3	2	1	*
4	1	1	*
5	2	2	*
6	7	5	71.43%
Sheet10

Hope that helps.

 

[VbaHell]

Thanks everyone for all your replies
Snakehips i went with your answer, simple formula for A6 (Doh, why did I not think of this)