Formula Needed for lowest three values in a Column

Thanks, Roger. I can't imagine what he would have said had I offered the
wrong advice.

Regards,
Fred

"Roger Govier" wrote in message
...
Hi Frank

Let's get one thing straight.
Fred was absolutely correct in suggesting
=A12=MIN(A12,D16,G12,K16,A24,D22,E22)

That does work. It does not require A12:A12 etc.

Fred, like many other regulars in these NG's has provided help and
assistance to thousands of people. Each of the respondents does not
receive any reward for their efforts, they give of their time and
knowledge freely, for the benefit of other.

Your abusive response to Fred virtually guarantees that you will receive
no further help from any other regular in these forums.

--
Regards
Roger Govier

frankjh19701 wrote:
O.K. O.K. O.K. First:
Using words like "Innocuous" and "disparate" and pointing out that I
could have "saved yourself a lot of time by posting this information
from the very beginning" just makes you sound like jerk. I apologize for
not having every last bit of information for you; due
to time constraints and the wealth of knowledge I've experienced
already from this group, I took for granted that what I set forth as a
question would be sufficient.

Second:
If I don't seperate the cells as their own ranges, such as A12:A12, the
formaula DOES NOT WORK.

Third:
I need to know how much of what is remaining cost. Yes, I could do the
"average", but that wouldn't tell me what I need to
extrapolate.

See, I can use big words too.

Cheers



'Fred Smith[_4_ Wrote:
;939129']You could have saved yourself a lot of time by posting this
information from the very beginning.

First, when you post a formula, *always* copy and paste it. *Never*
just type it in to your message. When you type it, typos creep in. Some
of
them are innocuous (like your G12:12), but others can cause problems
which
result in lost time or bad advice, or both.

Second, you don't need to repeat your cell addresses if there's only
one in the range.
=A12=MIN(A12,D16,G12,K16,A24,D22,E22)
would work just as well.

Third, in order to get the 2nd smallest, you need to use the Small
function. Because you have disparate cells, you need to define a name
for them.
For example, define the name Results as cells
A12,D16,G12,K16,A24,D22,E22.
Then use the formula:
=A12=SMALL(Results,1) to get the smallest,
=A12=SMALL(Results,2) to get the 2nd smallest, and
=A12=SMALL(Results,3) to get the 3rd smallest

Finally, on your inventory question, why maintain the "layers"? Why not
just calculate the average cost and use it?

Regards,
Fred



"frankjh19701" wrote in message

...-
Sorry about that. Here's what I have and what I need:

I have values in Cells

A12 D16 G12 K16 A24 D22 E22

I need to find the lowest three values and color code them in three
different colors to tell them apart.

I've been using Conditional Formatting with the formula
=A12=MIN(A12:A12, D1616, G12:12, K16:K16, A24:A24, D2222,
E22:E22)
And this only works to find the lowest.

Also,
on another note:

I need to come up with a way to show the different values of stock
prices when purchased. For example,

You start off with 12 items at a cost of $2.00 each, as you sell
items,
you replace the inventory, but the cost goes up or down. You sell
off
five items and receive a delivery of 8 more at a cost of $2.13. How
can
I show the different "Layers" of cost of the inventory?

Thank you again for all of your assistance.
'Fred Smith[_4_ Wrote:-
;933870']Let's assume you want to find the lowest three numbers in
column A, starting
in A1, and you are using XL2007. In the future, if you want more
specific
instructions, provide sufficient information so we don't have to
make
assumptions.

Select A1.
Click Conditional FormattingNew Rule...Use a formula to determine
which
cells to format
In the box Format values where this formula is true, enter:
=a1=small(a:a,1)
Click Format...Fill, choose a color, click OK.
Do this again for the second smallest, using the formula:
=a1=small(a:a,2), and choose a different color.
For the third smallest, use:
=a1=small(a:a,3), and its color.

Now copy this formatting to the rest of the cells in your range.
The
easiest
way to do this is to right-drag the fill handle down the column.
When
you
release the mouse button, choose Fill Formatting Only.

Regards,
Fred


"frankjh19701" wrote in
message
...--
'Fred Smith[_4_ Wrote:-
;930040']Use conditional formatting, and a formula like:
=a1=small(a:a,1)

Regards,
Fred



"frankjh19701" wrote in-
message-
...--
I need a formula that will find the lowest three values in a-
given--
row-
or column. I would like to be able to highlight them in
different
colors.

Any/all assistance is greatly appreciated.

Thank you




--
frankjh19701 ---
Thank you for responding
Ummm, O.K. how would I accomplish this with multiple cells from-
around-
the spreadsheet?
I think I need a little bit more help.




--
frankjh19701 ---



--
frankjh19701 -

Hi Frank

Thank you for your apology to Fred.
Whether he sees it or not, will depend upon whether he has added your
address to his "do not read" filter.

It does not make any difference if you are using any version of XL from
97 through to 2010, the formula Fred gave will work.

If you are still experiencing a problem, mail me the workbook direct,
and i will take a look.

To mail direct
roger at technology4u dot co dot uk
Change the at and dots to make a valid email address.
--
Regards
Roger Govier

frankjh19701 wrote:
Absolute apologies all around. When I read the post, it sounded
sarcastic, so I responded in the like.

I have tried using the formula as suggested and it will not work. Does
it make a difference if I am using Excel 2003 or not? I'm using Excel
2003 and the only way I get it to work is if I use the format A12:A12.
I cannot explain it.

I have been posting questions and such for not nearly as long as
others; and I am incredibly grateful for any/all of the assistance I
have received. And in no way, shape or form wish to come off as
ungrateful.

If this is the last we relay, or Fred, then so be it.

I've explained my position and apologized.

'Roger Govier[_8_ Wrote:
;939583']Hi Frank

Let's get one thing straight.
Fred was absolutely correct in suggesting
=A12=MIN(A12,D16,G12,K16,A24,D22,E22)

That does work. It does not require A12:A12 etc.

Fred, like many other regulars in these NG's has provided help and
assistance to thousands of people. Each of the respondents does not
receive any reward for their efforts, they give of their time and
knowledge freely, for the benefit of other.

Your abusive response to Fred virtually guarantees that you will
receive
no further help from any other regular in these forums.

--
Regards
Roger Govier

frankjh19701 wrote:-
O.K. O.K. O.K.
First:
Using words like "Innocuous" and "disparate" and pointing out that I
could have "saved yourself a lot of time by posting this information
from
the very beginning" just makes you sound like jerk.

I apologize for not having every last bit of information for you;
due
to time constraints and the wealth of knowledge I've experienced
already from this group, I took for granted that what I set forth as
a
question would be sufficient.

Second:
If I don't seperate the cells as their own ranges, such as A12:A12,
the
formaula DOES NOT WORK.

Third:
I need to know how much of what is remaining cost.
Yes, I could do the "average", but that wouldn't tell me what I need
to
extrapolate.

See, I can use big words too.

Cheers



'Fred Smith[_4_ Wrote: -
;939129']You could have saved yourself a lot of time by posting
this
information from
the very beginning.

First, when you post a formula, *always* copy and paste it. *Never*
just
type it in to your message. When you type it, typos creep in. Some
of
them
are innocuous (like your G12:12), but others can cause problems
which
result
in lost time or bad advice, or both.

Second, you don't need to repeat your cell addresses if there's
only
one in
the range.
=A12=MIN(A12,D16,G12,K16,A24,D22,E22)
would work just as well.

Third, in order to get the 2nd smallest, you need to use the Small
function.
Because you have disparate cells, you need to define a name for
them.
For
example, define the name Results as cells
A12,D16,G12,K16,A24,D22,E22.
Then
use the formula:
=A12=SMALL(Results,1) to get the smallest,
=A12=SMALL(Results,2) to get the 2nd smallest, and
=A12=SMALL(Results,3) to get the 3rd smallest

Finally, on your inventory question, why maintain the "layers"? Why
not
just
calculate the average cost and use it?

Regards,
Fred



"frankjh19701" wrote in
message
...--
Sorry about that. Here's what I have and what I need:

I have values in Cells

A12 D16 G12 K16 A24 D22 E22

I need to find the lowest three values and color code them in
three
different colors to tell them apart.

I've been using Conditional Formatting with the formula
=A12=MIN(A12:A12, D1616, G12:12, K16:K16, A24:A24, D2222,-
E22:E22)-
And this only works to find the lowest.

Also,
on another note:

I need to come up with a way to show the different values of stock
prices when purchased. For example,

You start off with 12 items at a cost of $2.00 each, as you sell-
items,-
you replace the inventory, but the cost goes up or down. You sell-
off-
five items and receive a delivery of 8 more at a cost of $2.13.
How-
can-
I show the different "Layers" of cost of the inventory?

Thank you again for all of your assistance.
'Fred Smith[_4_ Wrote:-
;933870']Let's assume you want to find the lowest three numbers
in
column A, starting
in A1, and you are using XL2007. In the future, if you want more
specific
instructions, provide sufficient information so we don't have to-
make-
assumptions.

Select A1.
Click Conditional FormattingNew Rule...Use a formula to
determine
which
cells to format
In the box Format values where this formula is true, enter:
=a1=small(a:a,1)
Click Format...Fill, choose a color, click OK.
Do this again for the second smallest, using the formula:
=a1=small(a:a,2), and choose a different color.
For the third smallest, use:
=a1=small(a:a,3), and its color.

Now copy this formatting to the rest of the cells in your range.-
The-
easiest
way to do this is to right-drag the fill handle down the column.-
When-
you
release the mouse button, choose Fill Formatting Only.

Regards,
Fred


"frankjh19701" wrote in-
message-
...--
'Fred Smith[_4_ Wrote:-
;930040']Use conditional formatting, and a formula like:
=a1=small(a:a,1)

Regards,
Fred



"frankjh19701" wrote in-
message-
...--
I need a formula that will find the lowest three values in a-
given--
row-
or column. I would like to be able to highlight them in-
different-
colors.

Any/all assistance is greatly appreciated.

Thank you




--
frankjh19701 ---
Thank you for responding
Ummm, O.K. how would I accomplish this with multiple cells from-
around-
the spreadsheet?
I think I need a little bit more help.




--
frankjh19701 ---


--
frankjh19701 ---


-

Thank you Roger. Hopefully, Fred will read it as well.

I did as you suggeested and emailed the workbook to you.
Please let me know what you come up with.

Thank you,
Frank

'Roger Govier[_8_ Wrote:
;939834']Hi Frank

Thank you for your apology to Fred.
Whether he sees it or not, will depend upon whether he has added your
address to his "do not read" filter.

It does not make any difference if you are using any version of XL from

97 through to 2010, the formula Fred gave will work.

If you are still experiencing a problem, mail me the workbook direct,
and i will take a look.

To mail direct
roger at technology4u dot co dot uk
Change the at and dots to make a valid email address.
--
Regards
Roger Govier

frankjh19701 wrote:-
Absolute apologies all around. When I read the post, it sounded
sarcastic, so I responded in the like.

I have tried using the formula as suggested and it will not work.
Does
it make a difference if I am using Excel 2003 or not? I'm using
Excel
2003 and the only way I get it to work is if I use the format
A12:A12.
I cannot explain it.

I have been posting questions and such for not nearly as long as
others; and I am incredibly grateful for any/all of the assistance I
have received. And in no way, shape or form wish to come off as
ungrateful.

If this is the last we relay, or Fred, then so be it.

I've explained my position and apologized.

'Roger Govier[_8_ Wrote: -
;939583']Hi Frank

Let's get one thing straight.
Fred was absolutely correct in suggesting
=A12=MIN(A12,D16,G12,K16,A24,D22,E22)

That does work. It does not require A12:A12 etc.

Fred, like many other regulars in these NG's has provided help and
assistance to thousands of people. Each of the respondents does not

receive any reward for their efforts, they give of their time and
knowledge freely, for the benefit of other.

Your abusive response to Fred virtually guarantees that you will
receive
no further help from any other regular in these forums.

--
Regards
Roger Govier

frankjh19701 wrote:--
O.K. O.K. O.K.
First:
Using words like "Innocuous" and "disparate" and pointing out that
I
could have "saved yourself a lot of time by posting this
information
from
the very beginning" just makes you sound like jerk.

I apologize for not having every last bit of information for you;-
due-
to time constraints and the wealth of knowledge I've experienced
already from this group, I took for granted that what I set forth
as-
a-
question would be sufficient.

Second:
If I don't seperate the cells as their own ranges, such as
A12:A12,-
the-
formaula DOES NOT WORK.

Third:
I need to know how much of what is remaining cost.
Yes, I could do the "average", but that wouldn't tell me what I
need-
to-
extrapolate.

See, I can use big words too.

Cheers



'Fred Smith[_4_ Wrote: -
;939129']You could have saved yourself a lot of time by posting-
this-
information from
the very beginning.

First, when you post a formula, *always* copy and paste it.
*Never*
just
type it in to your message. When you type it, typos creep in.
Some-
of-
them
are innocuous (like your G12:12), but others can cause problems-
which-
result
in lost time or bad advice, or both.

Second, you don't need to repeat your cell addresses if there's-
only-
one in
the range.
=A12=MIN(A12,D16,G12,K16,A24,D22,E22)
would work just as well.

Third, in order to get the 2nd smallest, you need to use the
Small
function.
Because you have disparate cells, you need to define a name for-
them.-
For
example, define the name Results as cells-
A12,D16,G12,K16,A24,D22,E22.-
Then
use the formula:
=A12=SMALL(Results,1) to get the smallest,
=A12=SMALL(Results,2) to get the 2nd smallest, and
=A12=SMALL(Results,3) to get the 3rd smallest

Finally, on your inventory question, why maintain the "layers"?
Why-
not-
just
calculate the average cost and use it?

Regards,
Fred



"frankjh19701" wrote in-
message-
...--
Sorry about that. Here's what I have and what I need:

I have values in Cells

A12 D16 G12 K16 A24 D22 E22

I need to find the lowest three values and color code them in-
three-
different colors to tell them apart.

I've been using Conditional Formatting with the formula
=A12=MIN(A12:A12, D1616, G12:12, K16:K16, A24:A24, D2222,-
E22:E22)-
And this only works to find the lowest.

Also,
on another note:

I need to come up with a way to show the different values of
stock
prices when purchased. For example,

You start off with 12 items at a cost of $2.00 each, as you
sell-
items,-
you replace the inventory, but the cost goes up or down. You
sell-
off-
five items and receive a delivery of 8 more at a cost of $2.13.-
How--
can-
I show the different "Layers" of cost of the inventory?

Thank you again for all of your assistance.
'Fred Smith[_4_ Wrote:-
;933870']Let's assume you want to find the lowest three
numbers-
in-
column A, starting
in A1, and you are using XL2007. In the future, if you want
more
specific
instructions, provide sufficient information so we don't have
to-
make-
assumptions.

Select A1.
Click Conditional FormattingNew Rule...Use a formula to-
determine-
which
cells to format
In the box Format values where this formula is true, enter:
=a1=small(a:a,1)
Click Format...Fill, choose a color, click OK.
Do this again for the second smallest, using the formula:
=a1=small(a:a,2), and choose a different color.
For the third smallest, use:
=a1=small(a:a,3), and its color.

Now copy this formatting to the rest of the cells in your
range.-
The-
easiest
way to do this is to right-drag the fill handle down the
column.-
When-
you
release the mouse button, choose Fill Formatting Only.

Regards,
Fred


"frankjh19701" wrote in-
message-
...--
'Fred Smith[_4_ Wrote:-
;930040']Use conditional formatting, and a formula like:
=a1=small(a:a,1)

Regards,
Fred



"frankjh19701" wrote
in-
message-
...--
I need a formula that will find the lowest three values in
a-
given--
row-
or column. I would like to be able to highlight them in-
different-
colors.

Any/all assistance is greatly appreciated.

Thank you




--
frankjh19701 ---
Thank you for responding
Ummm, O.K. how would I accomplish this with multiple cells
from-
around-
the spreadsheet?
I think I need a little bit more help.




--
frankjh19701 ---


--
frankjh19701 ---


---



-




--
frankjh19701