Hi, I am trying complete a project where I need to use nested functions. The
scenario is I have 4 amounts that are grades. The lowest grade is dropped,
and the remaining 3 are averaged to give the grade. I have to use the SUM,
MIN and COUNT function within the same cell address to get the answer.
Here is where I'm stuck...
=SUM(B4:E4)-MIN(B4:E4)
I'm not sure how to use the COUNT function to give me a 3 since all 4 cells
will have amounts? Plus how will I get the above to give me an average? ANy
help appreciated so I can sleep. :-)

---look like this ?
The lowest grade is dropped,
and the remaining 3 are averaged to give the grade.

average grade=(SUM(B4:E4)-MIN(B4:E4))/3

same with me, need more sleep....

"mrfrenchy" wrote:

Hi, I am trying complete a project where I need to use nested functions. The
scenario is I have 4 amounts that are grades. The lowest grade is dropped,
and the remaining 3 are averaged to give the grade. I have to use the SUM,
MIN and COUNT function within the same cell address to get the answer.
Here is where I'm stuck...
=SUM(B4:E4)-MIN(B4:E4)
I'm not sure how to use the COUNT function to give me a 3 since all 4 cells
will have amounts? Plus how will I get the above to give me an average? ANy
help appreciated so I can sleep. :-)

I've seen this exact same question before. This must stump some students!

I'm not sure how to use the COUNT function to give me a 3 since all 4
cells
will have amounts?

Well, think about it. You have 4 numbers but you want to exclude the
minimum. So, if COUNT(B4:E4) = 4 then you need to subtact 1 for the minimum.

The answer that's expected:

=(SUM(B4:E4)-MIN(B4:E4))/(COUNT(B4:E4)-1)

Now, here's how you would do this in the real world:

=AVERAGE(SMALL(B4:E4,{2,3,4}))

However, you'd be assuming that there are in fact 4 numbers in the range. If
you want to be really robust about it:

=IF(COUNT(B4:E4)=0,0,IF(COUNT(B4:E4)1,(SUM(B4:E4)-MIN(B4:E4))/(COUNT(B4:E4)-1),SUM(B4:E4)))

Biff

"mrfrenchy" wrote in message
...
Hi, I am trying complete a project where I need to use nested functions.
The
scenario is I have 4 amounts that are grades. The lowest grade is
dropped,
and the remaining 3 are averaged to give the grade. I have to use the
SUM,
MIN and COUNT function within the same cell address to get the answer.
Here is where I'm stuck...
=SUM(B4:E4)-MIN(B4:E4)
I'm not sure how to use the COUNT function to give me a 3 since all 4
cells
will have amounts? Plus how will I get the above to give me an average?
ANy
help appreciated so I can sleep. :-)

in order to complete it and have an average of exactly for 3 of 4, dont leave
the cells as blank, so you have a rated grade not a blank grade....same as
our teachers did...so we can sleep...thanks..

"mrfrenchy" wrote:

Hi, I am trying complete a project where I need to use nested functions. The
scenario is I have 4 amounts that are grades. The lowest grade is dropped,
and the remaining 3 are averaged to give the grade. I have to use the SUM,
MIN and COUNT function within the same cell address to get the answer.
Here is where I'm stuck...
=SUM(B4:E4)-MIN(B4:E4)
I'm not sure how to use the COUNT function to give me a 3 since all 4 cells
will have amounts? Plus how will I get the above to give me an average? ANy
help appreciated so I can sleep. :-)

thanks bifff. must be in a real world...no blanks...got ot go....

"Biff" wrote:

I've seen this exact same question before. This must stump some students!

I'm not sure how to use the COUNT function to give me a 3 since all 4
cells
will have amounts?

Well, think about it. You have 4 numbers but you want to exclude the
minimum. So, if COUNT(B4:E4) = 4 then you need to subtact 1 for the minimum.

The answer that's expected:

=(SUM(B4:E4)-MIN(B4:E4))/(COUNT(B4:E4)-1)

Now, here's how you would do this in the real world:

=AVERAGE(SMALL(B4:E4,{2,3,4}))

However, you'd be assuming that there are in fact 4 numbers in the range. If
you want to be really robust about it:

=IF(COUNT(B4:E4)=0,0,IF(COUNT(B4:E4)1,(SUM(B4:E4)-MIN(B4:E4))/(COUNT(B4:E4)-1),SUM(B4:E4)))

Biff

"mrfrenchy" wrote in message
...
Hi, I am trying complete a project where I need to use nested functions.
The
scenario is I have 4 amounts that are grades. The lowest grade is
dropped,
and the remaining 3 are averaged to give the grade. I have to use the
SUM,
MIN and COUNT function within the same cell address to get the answer.
Here is where I'm stuck...
=SUM(B4:E4)-MIN(B4:E4)
I'm not sure how to use the COUNT function to give me a 3 since all 4
cells
will have amounts? Plus how will I get the above to give me an average?
ANy
help appreciated so I can sleep. :-)

amazing! I couldn't get the /COUNT function to work for me. Thanks, here
comes the Ace in the class. Cheers to you guys! Nighty Night!

"4pinoy" wrote:

in order to complete it and have an average of exactly for 3 of 4, dont leave
the cells as blank, so you have a rated grade not a blank grade....same as
our teachers did...so we can sleep...thanks..

"mrfrenchy" wrote:

Hi, I am trying complete a project where I need to use nested functions. The
scenario is I have 4 amounts that are grades. The lowest grade is dropped,
and the remaining 3 are averaged to give the grade. I have to use the SUM,
MIN and COUNT function within the same cell address to get the answer.
Here is where I'm stuck...
=SUM(B4:E4)-MIN(B4:E4)
I'm not sure how to use the COUNT function to give me a 3 since all 4 cells
will have amounts? Plus how will I get the above to give me an average? ANy
help appreciated so I can sleep. :-)

Hi,

How about =(SUM(B4:E4)-MIN(B4:E4))/count(b4:e4)-1

Is this waht you need?
Mondo


"mrfrenchy" je napisal:

Hi, I am trying complete a project where I need to use nested functions. The
scenario is I have 4 amounts that are grades. The lowest grade is dropped,
and the remaining 3 are averaged to give the grade. I have to use the SUM,
MIN and COUNT function within the same cell address to get the answer.
Here is where I'm stuck...
=SUM(B4:E4)-MIN(B4:E4)
I'm not sure how to use the COUNT function to give me a 3 since all 4 cells
will have amounts? Plus how will I get the above to give me an average? ANy
help appreciated so I can sleep. :-)

after some sleep it look like this ?
The lowest grade is dropped,
and the remaining 3 are averaged to give the grade.
Formula condition to filled in amounts on 4 cells.
If one cell is blank means no amount....means grades incomplete and not
satisfied the averaging criteria...
HENCE : average grade
=IF(OR(B4="",C4="",D4="",E4=""), "INCOMPLETE GRADE NOT
VERIFIED",(SUM(B4:E4)-MIN(B4:E4))/3)

for visual effects on B4
click formatconditional formattingformula
tYpe in ) =B4=""
Click format Patterns click gray color
Copypaste from B4 to E4
gray means no amount or value

Note : A "0" typed in any cell means an amount.....

hope u dont need a bunch of nesting formula....


"mrfrenchy" wrote:

Hi, I am trying complete a project where I need to use nested functions. The
scenario is I have 4 amounts that are grades. The lowest grade is dropped,
and the remaining 3 are averaged to give the grade. I have to use the SUM,
MIN and COUNT function within the same cell address to get the answer.
Here is where I'm stuck...
=SUM(B4:E4)-MIN(B4:E4)
I'm not sure how to use the COUNT function to give me a 3 since all 4 cells
will have amounts? Plus how will I get the above to give me an average? ANy
help appreciated so I can sleep. :-)

zzzzzzzz??????

"mrfrenchy" wrote:

Hi, I am trying complete a project where I need to use nested functions. The
scenario is I have 4 amounts that are grades. The lowest grade is dropped,
and the remaining 3 are averaged to give the grade. I have to use the SUM,
MIN and COUNT function within the same cell address to get the answer.
Here is where I'm stuck...
=SUM(B4:E4)-MIN(B4:E4)
I'm not sure how to use the COUNT function to give me a 3 since all 4 cells
will have amounts? Plus how will I get the above to give me an average? ANy
help appreciated so I can sleep. :-)