Multiple functions in one cell

[reid1201]

In a single cell, I want to sum a range of numbers, then if that sum meets a certain criterion, then the cell will produce a result. for example: in G4, I want to sum A4:F4, if that sum = 12, then I want it to show the date listed in cell G3.

Is this possible?

Steven

 

[bluemjk]

This worked. HOWEVER (again), it appears that we have another issue. In cell F18 I would need to have the formula calculate the average. But here is where it gets tricky. I realize that my figures are skewed if I use 0 as N/A in my calculations. 100% and 77.8% divided by 3 gives me a different figure (59%) than dividing by 2 (88%). I have played with the formula and am again having a rough time with it. I don't know if there is a formula that you could devise that would divide by 3 unless one of the cells are N/A in which case you would divide by 2.

You have been extremely helpful and I have already learned alot..

 

[Benvolio]

You might need to upload a sample of the data so we can see what you mean; I'm having a little trouble visualizing it. If my understanding is correct though, this formula in F18 might help:

=IFERROR(IF(VLOOKUP("N/A",F9:F15,1)="N/A",SUM(F9:F15)/2),SUM(F9:F15)/3)

I've used a VLOOKUP function here to check all three of your variables for "N/A". I'm sure there are more elegant ways to do this, but this should do the trick. Let us know how you get on!

 

[mikerickson]

If you are averaging percentages, the result will be unreliable unless you are sure that the percentages come from equal sized samples.

Also COUNT(A1, B3, C8) will return the number of cells that have numbers in them. If B3 has an error value and A1, C8 have number, it will return 2.
That could serve as your divisor.

 

[Benvolio]

Good point with the percentage samples. Great point also with the COUNT function; assuming the percentages are safe to average, you can get away with it as simply as:

=SUM(F9:F13)/COUNT(F9,F11,F13)

I knew there would be a more elegant way to do it!

 

[mikerickson]

For this case, the SUM will have to be changed so that errors are ignored rather than erroring the sum. Repeated IFERROR(F9,0)+IFERROR(F10,0)+...

 

[Benvolio]

I think I must be misunderstanding the original requirements here, but it was my understanding that the instance of N/A is an actual "N/A" text string, rather than an #N/A error. If this is not the case, then Mike's answers are correct; my answers above will produce errors unless the #N/A error is neutralized.

 

[bluemjk]

Hi Guys,

Below is an except from the spreadsheet. The figures in bold identify columns F9, F12, F15 and the bold/underlined cell is F18. I am getting 5.9% because the formula that Mike last gave me counts #N/A as 0 (=(IFERROR(F9,0)+IFERROR(F12,0)+IFERROR(F15,0))*10/3), my fault Mike for not explaining everything correctly. Hope this helps you help me. LOL Thanks a bunch guys. I have not tried any of the other formulas provided yet, juggling two other projects at the moment.

ECS

Percentage units Received on time		100.0%	88.9%	100.0%	100.0%	75.0%
Failure Rate (ECS)	100.0%	0.0%	11.1%	0.0%	0.0%	25.0%
EDM
Percentage units Received on time		66.7%	27.0%	100.0%	77.8%	50.0%
Failure Rate (EDM)	100.0%	33.3%	73.0%	0.0%	22.2%	50.0%
Quality Machining
Percentage units Received on time		27.2%	70.0%	100.0%	#N/A	92.8%
Failure Rate (QMAC)	100.0%	72.8%	30.0%	0.0%	#N/A	7.2%
Average Supplier Performce Health Score	0.0	6.5	6.2	10.0	5.9	7.3

<colgroup><col><col span="3"><col><col span="2"></colgroup><tbody>
</tbody>

 

[Andrew Poulsom]

Is this what you want?

=SUM(IFERROR(F9,0),IFERROR(F12,0),IFERROR(F15,0))*10/COUNT(F9,F12,F15)

 

[bluemjk]

You guys are the best! I have much to learn. Thank you ...