#### 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.

# What is wrong with this formula in totaling in parent row?

Options
edited 12/09/19

I have a parent row I just can not get to toal the "Win Ratio" (it is a percent) if there is a ZERO in one of the child rows! It is driving me crazy!

The colums are:

Phone Calls | Accts Created | Win Ratio

The Daily formula works even when there is a ZERO in it... I am using this:

=IF(AND(ISNUMBER([Phone Calls]18), ISNUMBER([Accts Created]18)), [Accts Created]18 / [Phone Calls]18)

For example:  40 Phone Calls with  20 Accts Created is a Win Ratio of 50%

I am just wanting the TOTAL of the "Win Ratio" in the Parent Row to work ( it is 7 rows Sun thru Sat) and until the last day is entered it is giving me a error in the parent row total

I am using this:(NOT WORKING)

=IF(AND(ISNUMBER([Win Ratio]17:[Win Ratio]23), ISNUMBER([Win Ratio]17:[Win Ratio]23)), AVG([Win Ratio]17:[Win Ratio]23))

is it becuse it is a % or should it be sum?  I tried them all!  I would prefer it is the average of what has been accomplised otherwise the "win ratio" for the week will be wrong until the last day is entered.  but if thats not possible at least an error free "win ratio" % total would be nice

Thank you in advance for any help from you smart ones out there

• edited 05/20/16
Options

Okay this is not an elegant solution, but I would maybe maybe make a checkbox coloumn and from there make it so that in the parents row it counts the total amount of phonecalls(check boxes) and then puts that up against the amount of checkboxes that are =1(that they are checked). Not elegant, but I think it will work for you

• ✭✭✭✭✭✭
Options

Course Bridge,

Will this work for you:

Parent level formuas displayed.

I'm not sure what problem you were seeing that made you want to use the ISNUMBER functions.

Hope this helps

Craig

• Options

thank you!!!!!!!!! i was getting all the different errors available this seems to be working thank you! thank you!

• ✭✭✭✭✭✭
Options

You are welcome.

Craig

• Options

I still cant get it to ignore zero!  here where u see the 3% is still wrong... it is averaging in 10 months of ZERO's i want it to only average if the value is over 1... so if we are into the year 4 months it is the average of 4 months of percentages not 12 months and 8 of them are still 0%... any ideas? i have tried everything;) need a brain !

• ✭✭✭✭✭✭
Options

I can't make out what the columns are.

February 0 | 10 | 10 | 25% | 50% don't make sense without context.

Craig

• Options
• Options

jan 0,10,5,10%,20%

feb 0,10,10,25%,50%

• ✭✭✭✭✭✭
Options

Course Bridge,

You said the 3% is wrong.

Isn't the monthly totals also wrong?

10 calls, 5 accounts created, isn't that 50% win, not 10%?

There's something I am not seeing.

If you can, if you share the sheet with me (jcwill23@gmail.com), I'll take a look when I have time this weekend.

Craig

This discussion has been closed.