Need Formula to SUM Averaged Numbers without going to Div/0 or Error

[unit285]

AveragesTest.xlsm

So let me try to explain what I need the formula in Subject1!D5 to do without confusing anyone too much.

First the Info Sheet has 1 purpose, to choose the way the formula calculates Subject1!C6, Subject1!C6 is given either the same value as Subject1!C5, or Twice the Value, or Triple the Value, depending on the setting on the Info Sheet.

Subject1!D5 needs to give an Average of Subject1!C5 and Subject1!C6, However, if one or the other = "", then it needs to take that into consideration and give the value of the other (non empty) cell instead. So if Subject1!C5 has a value of 100, and Subject1!C6 has a value of 50, then the formula for Subject1!D5 should show a value of 75, but if Subject1!C5 is empty, then the formula for Subject!D5 should show a value of 50, likewise, if Subject1!C6 is empty, the formula for Subject1!D5 should show a value of 100. Other wise it should show the Average of Subject1!C5 and Subject1!C6 with Subject1!C6 given the added value of the setting on the Info Sheet. Hope I have not confused anyone. Thanks for your help! Below is my failed Formula.

Please Login or Register  to view this content.

[ben_hensel]

I think plain old AVERAGE(C5:C6) fills your need.

[Glenn Kennedy]

I tidied your formula up a bit. It's fine... Nothing wrong with it. It didn't like the space in C6. Delete it!!!

=IFERROR(IF(OR(Info!C3="",Info!C3="- Select -"),"",IF(Info!C3="1 - 1",(C6+C5)/2,IF(Info!C3="2 - 1",(C6*2+C5)/3,IF(Info!C3="3 - 1",(C6*3+C5)/4,"")))),"Error")

[unit285]

Hello and thanks for replying, the tidied up version of my formula still does not work for me.. For Example, if you put that formula in Subject1!D5 and then have a value of 95 in Subject1!C5 and no value in Subject1!C6, then the formula gives a result of 32, it should give a result of 95

[Glenn Kennedy]

Hang on a minute... Look at the formula, with 95 in C5, nothing in C6 and 2-1 selected on the first sheet.

IF(Info!C3="2 - 1",(SUM(C6*2+C5)/3) - your original formula...
IF(Info!C3="2 - 1",(C6*2+C5)/3 - my variant

BOTH calculate as 95/3.... So if you were expecting 95, then there's something fundamentally wrong with your formula. So, why do you expect 95 in this case.

[ben_hensel]

Wait, I see that it needs to be a weighted average?

Try this:

Please Login or Register  to view this content.

[unit285]

Hang on a minute... Look at the formula, with 95 in C5, nothing in C6 and 2-1 selected on the first sheet.

IF(Info!C3="2 - 1",(SUM(C6*2+C5)/3) - your original formula...
IF(Info!C3="2 - 1",(C6*2+C5)/3 - my variant

BOTH calculate as 95/3.... So if you were expecting 95, then there's something fundamentally wrong with your formula. So, why do you expect 95 in this case.

I know my formula is wrong, I will write out a formula that in my mind should work, but it doesn't And maybe you can see the problem with it.

Something along these lines but the formula is just a bit over my head and it does not work as written.. syntax is all wrong for starters

Please Login or Register  to view this content.

[unit285]

Wait, I see that it needs to be a weighted average?

Try this:

Please Login or Register  to view this content.

This formula has some interesting results.. but it still does not give me the numbers I'm looking for, and when both C5 and C6 are empty i get the div/0 error. also when i increase the setting on the Info Sheet it gives decreased value to C6 rather then increased value

[unit285]

Usually I can think through these things fairly well and can write some pretty complicated formulas.. but today my mind is a complete blank and I find myself getting frustrated instead of rethinking thru the problem. Why are my Mondays like this every freaking time!

[unit285]

Ok so I think I solved my problem by adjusting me formula just a bit.

This seems to do what I need it to do.

Please Login or Register  to view this content.

Thanks a lot for your help guys! It stimulated my mind for sure!