Formula help

VbaHell

Well-known Member

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

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

godsaaint

Active Member
Try (format the cell as %):

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

AliGW

Banned
You might consider the AVERAGEIF function.

Tetra201

MrExcel MVP
Is this what you need?

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

Snakehips

Well-known Member
Another variant..
Excel Workbook
ABC
121*
22N/A*
321*
411*
522*
67571.43%
Sheet10

Hope that helps.

VbaHell

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

Replies
6
Views
314
Replies
1
Views
246
Replies
3
Views
496
Replies
0
Views
244
Replies
6
Views
322

1,195,638
Messages
6,010,861
Members
441,571
Latest member
stolenweasel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back