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

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

Find more posts tagged with

Sales

Formulas

Conditional Formatting

Comments

Ichiorochi

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

J. Craig Williams

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

Course Bridge

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

J. Craig Williams

You are welcome.

Craig

Course Bridge

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 !

ss cap.png

J. Craig Williams

I can't make out what the columns are.

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

Craig

Course Bridge

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

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

J. Craig Williams

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