Comparing cells and output text
 

Hi!

I'm comparing prices of different companies and products.
If I have prices in different cells for each company I can
calculate the lowest price using:

=MIN(A1;B1;C1;D1)

The output is written in E1. How will I have cell F1 write
the company name? Let's say the companies are IBM,
MS, DELL, HP.

If MS has the lowest price I will have the value of B1 in
cell E1 and in cell F1 I will have the text MS.

It is simple to to with an IF statement when you only have
two companies. But what if you have three or more? Is there
a formula to use for a general case with many companies?

I am also curious if it it possible to change the color of cell
F1 depending on the text there? Let's say blue for MS, red
for Dell etc.

Thanks!

Comparing cells and output text
 

To return the Company names to column F try the VLOOKUP function.

Debra Dalgleish has goo instructions and a downloadable workbook.

http://www.contextures.on.ca/xlFunctions02.html

For changing cells colors based on cell value........text or numeric see
Conditional Formatting

See also Debra's site at http://www.contextures.on.ca/xlCondFormat01.html for
more on CF


Gord Dibben MS Excel MVP

On Sat, 17 May 2008 13:24:00 -0700, Rocketeer
wrote:

Hi!

I'm comparing prices of different companies and products.
If I have prices in different cells for each company I can
calculate the lowest price using:

=MIN(A1;B1;C1;D1)

The output is written in E1. How will I have cell F1 write
the company name? Let's say the companies are IBM,
MS, DELL, HP.

If MS has the lowest price I will have the value of B1 in
cell E1 and in cell F1 I will have the text MS.

It is simple to to with an IF statement when you only have
two companies. But what if you have three or more? Is there
a formula to use for a general case with many companies?

I am also curious if it it possible to change the color of cell
F1 depending on the text there? Let's say blue for MS, red
for Dell etc.

Thanks!

Comparing cells and output text
 

Q1:
This might suffice
In E1: =INDEX({"IBM","MS","DELL","HP"},MATCH(MIN(A1:D1),A 1:D1,0))

Q2:
You could use CF for col F, but only up to 3 colours (in xl2003)
Steps would go something like this ..
Select col F (F1 active), click Format Conditional Formatting
Condition 1
Formula Is: =$E1="MS"
Format to taste

Condition 2
Formula Is: =$E1="DELL"
Format to taste

Condition 3
Formula Is: =$E1="IBM"
Format to taste
OK out
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Rocketeer" wrote:
I'm comparing prices of different companies and products.
If I have prices in different cells for each company I can
calculate the lowest price using:

=MIN(A1;B1;C1;D1)

The output is written in E1. How will I have cell F1 write
the company name? Let's say the companies are IBM,
MS, DELL, HP.

If MS has the lowest price I will have the value of B1 in
cell E1 and in cell F1 I will have the text MS.

It is simple to to with an IF statement when you only have
two companies. But what if you have three or more? Is there
a formula to use for a general case with many companies?

I am also curious if it it possible to change the color of cell
F1 depending on the text there? Let's say blue for MS, red
for Dell etc.

Thanks!

Comparing cells and output text
 

Thanks for the links. Lots of useful information.

/Peter

"Gord Dibben" wrote:

To return the Company names to column F try the VLOOKUP function.

Debra Dalgleish has goo instructions and a downloadable workbook.

http://www.contextures.on.ca/xlFunctions02.html

For changing cells colors based on cell value........text or numeric see
Conditional Formatting

See also Debra's site at http://www.contextures.on.ca/xlCondFormat01.html for
more on CF


Gord Dibben MS Excel MVP

On Sat, 17 May 2008 13:24:00 -0700, Rocketeer
wrote:

Hi!

I'm comparing prices of different companies and products.
If I have prices in different cells for each company I can
calculate the lowest price using:

=MIN(A1;B1;C1;D1)

The output is written in E1. How will I have cell F1 write
the company name? Let's say the companies are IBM,
MS, DELL, HP.

If MS has the lowest price I will have the value of B1 in
cell E1 and in cell F1 I will have the text MS.

It is simple to to with an IF statement when you only have
two companies. But what if you have three or more? Is there
a formula to use for a general case with many companies?

I am also curious if it it possible to change the color of cell
F1 depending on the text there? Let's say blue for MS, red
for Dell etc.

Thanks!

Comparing cells and output text
 

Hi Max!

I can see were you want to go with the formulas. I'm having trouble
with the MATCH function. Lets say I don't have data in A1:D1 but
rather in F2, J2, N2 and R2.

=MATCH(MIN(F2;J2;N2;R2);(F2;J2;N2;R2);0)

But this outputs a #N/A in the target cell V2. Do you have any
suggestions?

Thanks!


"Max" wrote:

Q1:
This might suffice
In E1: =INDEX({"IBM","MS","DELL","HP"},MATCH(MIN(A1:D1),A 1:D1,0))

Q2:
You could use CF for col F, but only up to 3 colours (in xl2003)
Steps would go something like this ..
Select col F (F1 active), click Format Conditional Formatting
Condition 1
Formula Is: =$E1="MS"
Format to taste

Condition 2
Formula Is: =$E1="DELL"
Format to taste

Condition 3
Formula Is: =$E1="IBM"
Format to taste
OK out
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Rocketeer" wrote:
I'm comparing prices of different companies and products.
If I have prices in different cells for each company I can
calculate the lowest price using:

=MIN(A1;B1;C1;D1)

The output is written in E1. How will I have cell F1 write
the company name? Let's say the companies are IBM,
MS, DELL, HP.

If MS has the lowest price I will have the value of B1 in
cell E1 and in cell F1 I will have the text MS.

It is simple to to with an IF statement when you only have
two companies. But what if you have three or more? Is there
a formula to use for a general case with many companies?

I am also curious if it it possible to change the color of cell
F1 depending on the text there? Let's say blue for MS, red
for Dell etc.

Thanks!

Comparing cells and output text
 

You need to pull it into a contiguous range,
eg using simple link formulas such as these:

in S2: =IF(F2="","",F2)
in T2: =IF(J2="","",J2)
in U2: =IF(N2="","",N2)
in V2: =IF(R2="","",R2)

Then, you could place in W2:
=INDEX({"IBM","MS","DELL","HP"},MATCH(MIN(S2:V2),S 2:V2,0))

Note that in the event of any ties in the minimum values,
W2 will return only the 1st match from the left

(amend the commas to semicolons to suit your settings)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Rocketeer" wrote:
Hi Max!

I can see were you want to go with the formulas. I'm having trouble
with the MATCH function. Lets say I don't have data in A1:D1 but
rather in F2, J2, N2 and R2.

=MATCH(MIN(F2;J2;N2;R2);(F2;J2;N2;R2);0)

But this outputs a #N/A in the target cell V2. Do you have any
suggestions?

Thanks!

Comparing cells and output text
 

Hi!

Now it works like a charm. Thanks for your help.

/Peter

"Max" wrote:

You need to pull it into a contiguous range,
eg using simple link formulas such as these:

in S2: =IF(F2="","",F2)
in T2: =IF(J2="","",J2)
in U2: =IF(N2="","",N2)
in V2: =IF(R2="","",R2)

Then, you could place in W2:
=INDEX({"IBM","MS","DELL","HP"},MATCH(MIN(S2:V2),S 2:V2,0))

Note that in the event of any ties in the minimum values,
W2 will return only the 1st match from the left

(amend the commas to semicolons to suit your settings)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Rocketeer" wrote:
Hi Max!

I can see were you want to go with the formulas. I'm having trouble
with the MATCH function. Lets say I don't have data in A1:D1 but
rather in F2, J2, N2 and R2.

=MATCH(MIN(F2;J2;N2;R2);(F2;J2;N2;R2);0)

But this outputs a #N/A in the target cell V2. Do you have any
suggestions?

Thanks!

Comparing cells and output text
 

Glad it did. Welcome.
Do take a moment to press the "Yes" button below ...
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Rocketeer" wrote:
Now it works like a charm. Thanks for your help.

/Peter