# Thread: If/Or formula- why does this not work

1. This formula does not seem to be accepted the formula below as a working formula, error msg.

=IF(OR(E2=”Project 1″,E2=”Project 2″,E2=”Project 3″),G2-F2,(G2-F2)*0.5)

It should give sum in column G, except for Admin, which should return 1/2 sum.

2. Make sure you use the proper double quotes, it looks like all the opening double quotes ” need to be replaced with "

3. Len,

I've always had problems with evaluating text strings in this manner. Here's a work around that works by stripping the text and also allows for trailing blanks.

=(G2-F2)*(IF(VALUE(RIGHT(TRIM(E2),1))<4,1,0.5))

This solution, however, does not allow for the return of a less than 1 Project number...

I hope this helps.

RG

4. The formula work correctly for me. What errors are reported?

cheers, Paul

5. Works for me as well

Code:
`=IF(OR(E2="Project 1",E2="Project 2",E2="Project 3"),G2-F2,(G2-F2)*0.5)`
Although as Jan says, the double quotes needed to be "

6. Interesting!

With Win 7 and Excel 2003 SP-3 I get as shown below when I typed the formula in.

The first time I tried it by pasting it in it acted like it was text. Then I edited it and deleted to the left of the Equal sign which provided the "The formula you typed contains an error." error box

When I pasted in AKW's version it worked fine! I wonder what my old eyes are not seeing?

RG

7. You can also shorten it a bit:

=(G2-F2)*IF(OR(E2={"Project 1","Project 2","Project 3"}),1,0.5)

8. Nice trick Rory! Now I see what I missed in my post above the E2= is missing on Project 3!

RG

9. Thanks everyone. Impressed anyone saw the " issue.
Still have a problem. The sum at H10 is not showing a proper value.

Len

10. Nothing attached...

11. Not sure what happened. Here is another try.[attachment=88650:If-Or-#2.xls]

Len

12. The sum is correct. The times you have displayed add up to 1.36 days. If you want hours, you'll have to use a custom format of [h]:mm.

Time to Excel is stored as a portion of a day. 12 hours = 0.5 days
Dates/Days in Excel are stored as a whole number.

13. mbarron,
Thank you.

What is the difference of [h]:mm and h:mm ?
Can I not also do: =SUM(H2:H7)*24
Your suggestion returns 32.45, not 32.75.

14. h:mm is in Days so 15 hours + 10 Hrs and 50 min would become 1 Day 1 Hour and 50 Min
with the h:mm format this would show as 1:50 because there are NO days in the format.

[h]:mm is in hours as total duration so it would show as 25:50

I think the 32.45 was probably 32:45 which is 32 Hrs and 45 Min which is the same as 32.75 Hours

15. AKW,
Thanks.
Is there a custom format for the hours with fraction [32.75] , not Hrs:minutes ? In other words, another method besides multiplying by 24?

I just realized the :45 = .75 connection and was going to post my epiphany when I saw AKW's response, but his explanation of the [ ] function in format and how Excel handles h:mm was very informative and helpful.

Side note:
Every contributor to this forum is informative and helpful. I am sure everyone who posts questions in this forum is appreciative of your efforts. I sure am.

Len

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•