Formula Needed for lowest three values in a Column

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

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

'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

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

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

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

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

Have a nice day.

Fred

"frankjh19701" wrote in message
...

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

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 -

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