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?
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
Comments
-
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
-
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
-
thank you!!!!!!!!! i was getting all the different errors available this seems to be working thank you! thank you!
-
You are welcome.
Craig
-
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 !
-
I can't make out what the columns are.
February 0 | 10 | 10 | 25% | 50% don't make sense without context.
Craig
-
jan 0,10,5,10%,20%
feb 0,10,10,25%,50%
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.1K Get Help
- 414 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives