Question answered >> If + And Formula

[TracyG]

Hi, I need a formula that will take 7 numbers (example 1,1,1,1,2,2,2), through out one of the high numbers and one of the low numbers then add up the other 5 numbers for a sum. Can someone help me? Thanks!

[duane]

if they are in separate cells, say a1:g1
then
=sum(a1:g1)-max(a1:g1)-min(a1:g1)

[TracyG]

Thanks for your help. Now my next formula I need help with is, if I have 5 numbers(example 5, 10,13, 16,18) I need to have it calculate what would be 1st, 2nd,3rd, 4th or 5th place. Lowest to highest.

[duane]

see SMALL or LARGE function

[TracyG]

Hi Duane,
Thanks for your help. I'm really close on this. When I use the SMALL function it brings back the correct information but I want it to put 1 for the smallest number, 2 for the next, 3 for the next, and so on. This is for the first 5 placements in a show.

[Carim]

Hi,

Use Rank(yourcell, yourrange)

do you really need ordinal numbers ?

If A1 is the rank ...

=A1&IF(AND(A1>=10,A1<=14),"th",CHOOSE(MOD(A1,10)+1,"th","st","nd","rd","th","th","th","th","th","th"))


HTH
Carim

[TracyG]

OK. Sorry I'm still having problems with this. Here's what I've got
N5, N6, N7, N8, N9, N10, N11 will have number in no particular order (5, 15, 10, 20, 25, 17, etc). In column O I want it to tell me which one is the lowest number and that equals 1, the next lowest number = 2, etc.

[Carim]

Hi again,

In cell M5 have you typed ...

Please Login or Register  to view this content.

and copied formula all the way down ... ?

HTH
Carim

[all4excel]

Hi again,

In cell M5 have you typed ...

Please Login or Register  to view this content.

and copied formula all the way down ... ?

HTH
Carim

Automated by using the ROW().
[ =ROW()&IF(AND(ROW()>=10,ROW()<=14),"th",CHOOSE(MOD(ROW(),10)+1,"th","st","nd","rd","th","th","th","th","th","th")) ]