SOLVED

=SUM(ITEMS_PERCENT*ITEMS_WORKDAYS)/SUM(ITEM_WORKDAYS) problem

Copper Contributor

Hello, so I have been following the tutorial of making a gantt sheet by The Office Lab (link:https://www.youtube.com/watch?v=OizqFlMtZLQ) and I ran into an error at around 2:55:42 when he begins to do an automatic percentage sum of the item percent's and work days. For some reason when I do the exact same code as him it is giving me an error #VALUE!. I typed in the code above in the title with my own values and rows and it will keep giving me an error when in the video his total comes out to 81%. Ive been stuck on this for a bit and its causing me to be at a standstill. If someone maybe knows why and could help that would be great!  (also I know his is on the W column and my is X. Its because I wanted extra space for other areas so that should not have any change towards solution)Here is my side of the codeHere is my side of the code

 

11 Replies
Are the values in column R really numbers? Please check with ISNUMBER().

@LilWraith The formula shown in the formula bar in the screenshot should work. Check that cell R19 is empty. In case you have accidentally entered a space, the formula will throw a #VALUE! error.

 

With a space in the selected cell:

Screenshot 2021-12-23 at 07.23.23.png

and without:

Screenshot 2021-12-23 at 07.23.40.png

So I went in to make sure that all my cells were just the number and am still receiving the same error. The main problem I think is from the combining of R16:R19 and X16:X19 but you would probably know better than me
I checked my R column and when I checked each row individually they came out true except for the blank row. When I check all of them together though I receive an error. I also checked my X column and had the same result.
Please check whether it is really blank or text with ISTEXT().

Wow I didn't know those were tools to use that actually helps a lot! Yes, R:19 is blank and all the rows in both columns show false for ISTEXT(). I also used the ISBLANK() on R:19 and got TRUE. Sadly it is still showing an error.

Here are some extra screenshots on the error and hopefully they may help a bit better?

 

Screenshot 2021-12-23 101715.png

 

Screenshot 2021-12-23 101743.png

 

Well, then it should...must work.
I know, that's why I'm so confused.
best response confirmed by LilWraith (Copper Contributor)
Solution
Except if you are using an older version of Excel. Excel 365 and Excel 2021 will work.
In older version you have to use the CTRL-SHIFT-ENTER keystroke instead of just ENTER.
Oh my! You're a life saver! It works perfectly now!
1 best response

Accepted Solutions
best response confirmed by LilWraith (Copper Contributor)
Solution
Except if you are using an older version of Excel. Excel 365 and Excel 2021 will work.
In older version you have to use the CTRL-SHIFT-ENTER keystroke instead of just ENTER.

View solution in original post