If/Or formula- why does this not work

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

cheers, Paul

Reply | Quote

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 “

Reply | Quote

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

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

You can also shorten it a bit:

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

Reply | Quote

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

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks everyone. Impressed anyone saw the ” issue.
Still have a problem. The sum at H10 is not showing a proper value.
Corrected spreadsheet attached.

Len

Reply | Quote

Nothing attached…

Reply | Quote

Not sure what happened. Here is another try.

Len

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

I do not believe so, since it is no longer a “time”.

Steve

Reply | Quote