Question about If using Min

[Jay Hanks]

My question is regarding using IF statements with MIN or MAX I am takin
a class and our spreadsheet deals with a car dealership and sales. I a
having problems with creating an IF statement, using MIN, to displa
who sold the least cars. I was able to create one for the MAX number o
cars sold.

Here is the MAX statement which works displaying “Hill”
=IF(J11=MAX($E$11:$J$11:$O$11:$E$20:$J$20),"Hill",IF(E11=MAX($E$11:$J$11:$O$11:$E$20:$J$20),"Hanks",IF(O11=MAX($E$11:$J$11:$O$11:$E$20:$J$20),"Sills",IF(E20=MAX($E$11:$J$11:$O$11:$E$20:$J$20),"Farmer",IF(J20=MAX($E$11:$J$11:$O$11:$E$20:$J$20),"Linnville","")))))

Here is the MIN statement I cannot get to work, it shoul
display”Hanks”

=IF(J11=MIN($E$11:$J$11:$O$11:$E$20:$J$20),"Hill",IF(E11=MIN($E$11:$J$11:$O$11:$E$20:$J$20),"Hanks",IF(O11=MIN($E$11:$J$11:$O$11:$E$20:$J$20),"Sills",IF(E20=MIN($E$11:$J$11:$O$11:$E$20:$J$20),"Farmer",IF(J20=MIN($E$11:$J$11:$O$11:$E$20:$J$20),"Linnville","")))))

The one problem I noticed is when I try to break this down and use i
in individual cells I only get False and no name at all. I did use thi
formula to verify that I could display the cell with the right answer.

=MIN($E$11,$J$11,$O$11,$E$20,$J$20)

Can anyone point me in the right direction here? Thanks Ja

 

[Earl Kiosterud]

Jay,

I'm getting dizzy. Why are you using $E$11:$J$11:$O$11:$E$20:$J$20? Which
cells do you intend for the MIN function to look at? Did it get changed to
$E$11:$J$11:$E$11:$O$20:$J$20? What do you get if you select the MIN
function within your formula and press F9?

 

[AlfD]

Hi!

Your MAX formula may look as though it it working, but don't believ
it!

Have a look first at your syntax. (The Excel Help on MAX will be a bi
useful...)

For example, I cannot interpret the "phrase"

J11=MAX($E$11:$J$11:$O$11:$E$20:$J$20)

Should this be referring to the maximum of the 5 cells within th
brackets? If so, try commas instead of colons. If some of the
genuinely are ranges (e.g. $E$11:$J$11) then these ranges must b
separated by commas.

Then have a look at the cells in your lists. I think you will find yo
have put E11 in twice and missed out E20 in a few places.

Similar considerations apply to your MIN formula.

Secondly: this doesn't strike me as the tidiest way of getting to you
goal.
The (composite) range $E$11,$J$11,$E$20,$O$20,$J$20 keeps cropping up
Why not give it a name? (Insert>Name>Define..)

Let's call it Sales. Now you can write
=if(J11=MAX(Sales),"Hill",.......)

But even then, Excel is forever calculating MAX(Sales). You might thin
of giving this a cell of its own. e.g. Set Q1=MAX(Sales).

Then you have =if(J11=Q1,"Hill",if E11=Q1,"Hanks".... and so on)

Then do something similar with MIN.

You can even make quantities more recognisable. For example, if yo
gave Q1 a name, say TopSales (Insert>Name>Define..)
you could have =if(J11=TopSales,"Hill",.....)

Finally (for me, anyway, at the moment) let's suppose that J11 hold
Hill's sales ('cos it looks like it does). You could even name thi
(same process) as "HillSales".

The end result might now be =if(HillSales=TopSales,"Hill",i
HanksSales=TopSales,"Hanks"......etc).



Al

 

[Jay Hanks]

Answers to questions, I suppose I should have added that I am new to th
formula’s in Excel, I am taking an intro class and some of this i
setup per the class directions.

"For example, I cannot interpret the "phrase"

J11=MAX($E$11:$J$11:$O$11:$E$20:$J$20)"

This is the general way someone who took the class set this up, it doe
work for Max, and in his spread sheet Min as well.
To paraphrase the assignment use a cell entry for who sold the mos
and least cars, use “IF” function to do this, it also mentions usin
the Min and Max functions.

So what I basically have is 5 separate salespeople, each set up for a
month listing of sales.J11,E11,O11,E20,J20 are where I have the tota
sales for that person stored.

My thought is using IF I want to compare all 5 total sales and when th
max is found display, in that case “ Hill” , in the least case, Min,
Hanks” .

Once again I am very new to building with formula’s so take that with
big grain of salt. The way the professor has shown that he has buil
them looks very much like what I have constructed.
Thanks Ja

 

[anonymous]

-----Original Message-----
Hi!

Your MAX formula may look as though it it working, but don't believe
it!

Have a look first at your syntax. (The Excel Help on MAX will be a bit
useful...)

For example, I cannot interpret the "phrase"

J11=MAX($E$11:$J$11:$O$11:$E$20:$J$20)

Should this be referring to the maximum of the 5 cells within the
brackets? If so, try commas instead of colons. If some of them
genuinely are ranges (e.g. $E$11:$J$11) then these ranges must be
separated by commas.

Then have a look at the cells in your lists. I think you will find you
have put E11 in twice and missed out E20 in a few places.

Similar considerations apply to your MIN formula.

Secondly: this doesn't strike me as the tidiest way of getting to your
goal.
The (composite) range $E$11,$J$11,$E$20,$O$20,$J$20 keeps cropping up.
Why not give it a name? (Insert>Name>Define..)

Let's call it Sales. Now you can write
=if(J11=MAX(Sales),"Hill",.......)

But even then, Excel is forever calculating MAX(Sales). You might think
of giving this a cell of its own. e.g. Set Q1=MAX(Sales).

Then you have =if(J11=Q1,"Hill",if E11=Q1,"Hanks".... and so on)

Then do something similar with MIN.

You can even make quantities more recognisable. For example, if you
gave Q1 a name, say TopSales (Insert>Name>Define..)
you could have =if(J11=TopSales,"Hill",.....)

Finally (for me, anyway, at the moment) let's suppose that J11 holds
Hill's sales ('cos it looks like it does). You could even name this
(same process) as "HillSales".

The end result might now be =if (HillSales=TopSales,"Hill",if
HanksSales=TopSales,"Hanks"......etc).



Alf

Using defined names has it advantages but it also has it's
disadvantages. Personally, I hate getting a WB
that's "broke" and having to track down all those dang
names. And then there's that editing thing. And that
teenie tiny little box: refers to!

 

[Leo Heuser]

Jay

Here's another option:

Make a namelist for the five employees in e.g. Q2:R6

In Q2 the formula =E11 in R2 Hanks
In Q3 the formula =J11 in R3 Hill
In Q4 the formula =O11 in R4 Sills
In Q5 the formula =E20 in R5 Farmer
In Q6 the formula =J20 in R6 Linnville

Q2:Q6 now contains the sales figures for each person,
and R2:R6 contains their names.

The list is easy to alter for more or fewer employees.

Now to get the name of the employee having the maximum sale
use this formula:

=VLOOKUP(MAX(Q2:Q6),Q2:R6,2,0)

To get the minimum use

=VLOOKUP(MIN(Q2:Q6),Q2:R6,2,0)

To make it more flexible, you could name the namelist e.g.
"Namelist" (Select Q2:R6 and click in the namebox
(to the left of the formula bar)). Enter "Namelist" (without quotes).
(Or use the menu Insert > Name > Define (see below))

Instead of the hardcoded formulae above, you can now use:

=VLOOKUP(MAX(OFFSET(namelist,,,,1)),namelist,2,0)

and

=VLOOKUP(MIN(OFFSET(namelist,,,,1)),namelist,2,0)

When the number of employees vary, you must edit the range
of "Namelist" to mirror the change. Do this in Insert > Name > Define,
but it's not necessary to alter the VLOOKUP()-formulae.

 

[Jay Hanks]

Thank you all, I will try some of these idea's. Ja