Excel - compare two number cells and output a text value

digamma · 12th July 2012, 11:53 AM

I'm a bit of a n00b when it comes to using excel; I can do basic arithmetic and budgets, but this has me stumped.

I'm trying to extend on a spreadsheet that shows a quote for computer systems. It lists the items with cost and then adds the cost up to give a total. On each sheet, it lists two different shops with the same or similar components. What I would like to do is compare the two shop quotes and output a tick or cross beside the item which shop is the cheapest for each item. I can do that for price, using the MIN function, but it will create an output for every item, not just the cheapest one. I was going to try and use an IF logic statement, but they don't go together, as the output from the MIN function is not true or false.

HELP!

ttv8 · 12th July 2012, 12:10 PM

AFAIK you can't do ticks or crosses unless use use the wingdings/symbol font.

If statements aren't always based on true false outputs, you can do ranges and "nested" if statements to get the level of filtering you want.

Not sure if I understood your question correctly, but this might help

Let's assume A10 and C10 are your prices, in E10 try

PHP Code:


			
=IF(A10<C10,"Yes, XYZ is cheaper",IF(C10<A10,"ABC cheaper",""))

digamma · 12th July 2012, 3:44 PM

Quote:

Originally Posted by ttv8

AFAIK you can't do ticks or crosses unless use use the wingdings/symbol font.

If statements aren't always based on true false outputs, you can do ranges and "nested" if statements to get the level of filtering you want.

Not sure if I understood your question correctly, but this might help

Let's assume A10 and C10 are your prices, in E10 try

PHP Code:


			
=IF(A10<C10,"Yes, XYZ is cheaper",IF(C10<A10,"ABC cheaper",""))

Worked like a charm, thanks for that. Something I realised after inserting that formula is if the prices are the same, it outputs nothing. And if there is a price in one shop's list, but not in the other shop's list, it will result in the missing shop's name being the result.

Ah ha, worked it out. Depended on the order of the arguments in the statement. This is what I ended up with:
=IF(C6+C26=0,"",IF(C6<C26,"Shop 1",IF(C6>C26,"Shop 2",IF(C6=C26,"Same"))))

12th July 2012, 11:53 AM	#1
digamma Member Join Date: Mar 2002 Location: Brisbane, Southside. Posts: 2,428	Excel - compare two number cells and output a text value I'm a bit of a n00b when it comes to using excel; I can do basic arithmetic and budgets, but this has me stumped. I'm trying to extend on a spreadsheet that shows a quote for computer systems. It lists the items with cost and then adds the cost up to give a total. On each sheet, it lists two different shops with the same or similar components. What I would like to do is compare the two shop quotes and output a tick or cross beside the item which shop is the cheapest for each item. I can do that for price, using the MIN function, but it will create an output for every item, not just the cheapest one. I was going to try and use an IF logic statement, but they don't go together, as the output from the MIN function is not true or false. HELP! __________________ The ultimate weakness of violence is that it is a descending spiral. Returning violence with violence only multiplies violence, adding deeper darkness to a night already devoid of stars.

12th July 2012, 12:10 PM	#2
ttv8 Member Join Date: Jan 2007 Location: Brisbane Posts: 469	AFAIK you can't do ticks or crosses unless use use the wingdings/symbol font. If statements aren't always based on true false outputs, you can do ranges and "nested" if statements to get the level of filtering you want. Not sure if I understood your question correctly, but this might help Let's assume A10 and C10 are your prices, in E10 try PHP Code: `=IF(A10<C10,"Yes, XYZ is cheaper",IF(C10<A10,"ABC cheaper",""))`