Return a cell's value in a formula when 0% is showing in the "completed" column.
I am trying to write a formula that will return the number of estimated hours still needed on a project. I have an "Estimated Hours Needed" column and a "Percent Completed Column".
My original calculation of
=SUM([Estimated Hours Needed]@row * [Percent Completed]@row)
worked for rows that had a percentage other than 0% in them. I tried another formula to tell it to see if the field had "0" in it  and if it did, use the number from the "Estimated Hours Needed" column, but if it had a number larger than "0", do the calculation of multiplying "Percentage Completed" against "Estimated Hours Needed". That formula returned an #Unparseable error for the row I put it in
Below is the formula I used:
=IF([Percent Completed]@row = "0", [Estimated Hours Needed@row], IF([Percent Completed]@row >"0", SUM([Estimated Hours Needed]@row * [Percent Completed]@row)))
What am I doing wrong?
Best Answer

That fixed the problem   thank you so much!!
Answers

Try this:
=IF([Percent Completed]@row = 0, [Estimated Hours Needed]@row, [Estimated Hours Needed]@row * [Percent Completed]@row)
Technically the only thing wrong with your formula was a misplaced closing square bracket there in the second cell reference, but the above will accomplish the same thing and work a little more efficiently on the backend.

That fixed the problem   thank you so much!!

Next step is to create a total for "Time Left" based on what the "Workflow" is.
I tried the formula below, but it didn't work. I'm not sure how to do a summation within a logic formula and haven't found any "cheat sheets" with that type of formula explanation.
=IF([Workflow@row, "Response Needed", SUM([Time Left]@row))
Any ideas? Thanks again for your help  greatly appreciated!

I'm not sure I follow. Are you trying to add up all [Time Left] entries that have a matching Workflow? If that is the case, take a look into the SUMIFS function.

That's the one I needed! I've used the "Count" formula, but haven't done the SUMIF one. Thank you for your help!

Help Article Resources
Categories
Check out the Formula Handbook template!