Share via

Excel formula/function correct, but not working.

Anonymous
2016-05-06T17:57:11+00:00

As you can see, when I open the dialog box for my formula it says the formula result should be 7.64. But once I press the "OK" button it instead shows the result of "0". Not sure why it is not working. My formulas are calculating automatically and I have actually switched it to manual then put it back to automatic. I have pressed the "Calculate Now" & "Calculate Sheet" button and both did not change anything, the result was still 0. I have actually deleted the formula then hand typed it back in again but it is still not working. I need this formula to work, I use this for payroll. It's so much easier if this formula would actually work instead of me doing a regular "=average" formula because I do not want to add the weekend zero hours into the formula.

Any suggestions for what to do? Thanks in advance!

Update, I found more formulas that are not being calculated even though I did the same as above. It's not calculating a simple formula "=sum(C6:R6)" I think it might be glitch/bug inside my worksheet. I guess I'm going to have to re-type my whole worksheet over again. I'll update if the problem stays.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. Anonymous
    2016-05-06T18:15:19+00:00

    I cannot reproduce this - see screenshot

    Can you post the file to OneDrive or similar file share site.

    OR would you are to visit my website (see people..... at bottom of this reply), get my email address and send me file by private email?

    Meanwhile try

    =SUMPRODUCT(--(D4:R4="Actual"), --(D6:R6>0), D6:R6)/SUMPRODUCT(--(D4:R4="Actual"), --(D6:R6>0))

    best wishes

    0 comments
  3. Anonymous
    2016-05-06T18:20:12+00:00

    Sometimes Excel gets the idea that all your numbers are text.

    Format an empty cell as General or Number then copy that cell.

    Select your range of "numbers" then paste special>add>ok>esc

    Any luck?

    Gord

    0 comments
  4. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  5. Anonymous
    2016-05-06T18:40:41+00:00

    Thanks for the reply,

    I formatted the range as numbers when I created the spreadsheet originally. I think there might be some sort of bug in my spreadsheet, before in the past it use to work. But ever since I sent it to one of my coworkers now it's not working. I guess I'll have to re-type it.

    0 comments