Page 1 of 1

Excel Formula please - comparing two fields.

Posted: Mon Oct 28, 2013 8:48 am
by simonmlewis
Hi.
I have an Excel sheet that I need to do some changes to help with a final figure.

So column B will either have (for example), Fred or Ginger in it. Column D will have some hours. Column E will have costs based on those hours.

I need in column F to check if B is "Fred" or "Ginger". And if it is either of those, to show what's in column E. If it's neither, it remains empty.

I've tried this:
=if(($B5="Fred"|"Ginger"), $E5))

But it doesn't like it. I use to be a real whiz with Excel, and did formulas that were amazing, but this was about 10+ years ago. Lost it all now.

Re: Excel Formula please - comparing two fields.

Posted: Mon Oct 28, 2013 9:40 am
by Christopher
I think it is OR, not |.

Re: Excel Formula please - comparing two fields.

Posted: Mon Oct 28, 2013 11:56 am
by simonmlewis
Great thanks. Excel's latest version has a Conditional Formatting tool, but what it fails to do, is let you set the colour of a field based on TWO sets of words. You can tell it to set the background colour of the word "Christopher" is in the field. But if you want "Christophier" or "Simon" in the field, it won't do it.

But, the original request is now sorted. :)

=IF(B42="Fred",E42,IF(B42="Ginger",E42,0))

Re: Excel Formula please - comparing two fields.

Posted: Mon Oct 28, 2013 2:27 pm
by requinix
There's also OR,

Code: Select all

=IF(OR(B42="Fred",B42="Ginger"),E42,0)
I think there's also a function equivalent to in_array() but I don't remember what it's called (or whether it can work on an inline list and not just a cell range).