Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    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. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Make sure you use the proper double quotes, it looks like all the opening double quotes ” need to be replaced with "
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    The formula work correctly for me. What errors are reported?

    cheers, Paul

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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 "
    Andrew

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can also shorten it a bit:

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

    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    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

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Nothing attached...
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Not sure what happened. Here is another try.[attachment=88650:If-Or-#2.xls]

    Len
    Attached Files Attached Files

  12. #12
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. #13
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    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. #14
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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
    Andrew

  15. #15
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    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 LastLast

Posting Permissions

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