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.
Excel Formula please - comparing two fields.
Moderator: General Moderators
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Excel Formula please - comparing two fields.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
-
simonmlewis
- DevNet Master
- Posts: 4435
- Joined: Wed Oct 08, 2008 3:39 pm
- Location: United Kingdom
- Contact:
Re: Excel Formula please - comparing two fields.
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))
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.
All the best from the United Kingdom.
Re: Excel Formula please - comparing two fields.
There's also OR,
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).
Code: Select all
=IF(OR(B42="Fred",B42="Ginger"),E42,0)