Excel Formula please - comparing two fields.

Ye' old general discussion board. Basically, for everything that isn't covered elsewhere. Come here to shoot the breeze, shoot your mouth off, or whatever suits your fancy.
This forum is not for asking programming related questions.

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Excel Formula please - comparing two fields.

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Excel Formula please - comparing two fields.

Post by Christopher »

I think it is OR, not |.
(#10850)
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Excel Formula please - comparing two fields.

Post 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))
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Excel Formula please - comparing two fields.

Post 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).
Post Reply