#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Formula Malfunction!

Options
edited 12/09/19

Has anyone every had a situation where a formula in a cell calculates improperly if it references a cell whose value is also derived based on a formula?

I have a formula set up where it's looking at cells in the current and previous rows to calculate total hours worked in a day. (I borrowed this formula from one of you lovely community members...thank you!) However, the formula only works if I type in the plain text value in the originating cells. When the value of those originating cells comes as the result of a formula, I get an incorrect calcluation.

Is this a known issue or is it more likely user-error? (I'm assuming the latter, at this point, but wanted to verify.) Are there any common mistakes I should be looking for when things like this happen?

I've included a screenshot of the erroneous calculation below. You can see the 3rd and 4th rows are giving strange results in the "Day Calc" field, given the formula, which should look to see if the previous row occurred on the same day and, if so, add today's hours to the total hours accumulated for that day. As I stated above, I found that if I manually change "hours worked" and just type in "0" and "1" for the 3rd and 4th rows, I get the proper "Day Calc" results of "1" and "2" for total hours worked for 2/28.

Tags:

• Employee
edited 02/28/17
Options

Your results are being added as strings because "01" is a string, so "1" + "01" is yeilding "101", likewise "a" + "bc" yeilds "abc"

Ensure the values in your cells are being calculated as numerics. [Day Calc]3 seems to be a string in this case (its left alignemnet shows this), but I couldnt tell you why unless I saw your formula for that cell as well.

• edited 02/28/17
Options

Thanks Benjamin!

I'm not sure how to change it to calculate as numerics. Can you help? Here's the formula you were asking about, as well as the one for [Day Calc]2. [Day Calc]1 is just the number "0" typed directly into the cell.

• edited 02/28/17
Options
• Employee
Options

Your Day Calc formulas are definitely good to go!

Sorry for the hastle, but would you mind pasting the Hours Worked formulas for  rows 3 and 4? That seems to be the offending formula here (hopefully)!

• ✭✭✭✭✭✭
Options

Tamara,

To use a text value as a number, wrap it in the VALUE() formula

For example, I can retrieve the month of a date field by usiing

LEFT([Date]23,2)

but that will be a text (for today's date, 03)

This:

=LEFT(Date23, 2) + 1

results in 031

(all text)

and this

=VALUE(LEFT(Date23,2)) + 1

results in 4

Hope this helps.

Craig

• Options

You guys are so awesome!

@J. Craig Williams - Wrapping in "VALUE" fixed my problem! (Plus, I learned something new and important!) Thanks a bunch.

• ✭✭✭✭✭✭
Options

Tamara,