Forum

Notifications
Clear all

get a percentage and return a zero

2 Posts
2 Users
0 Reactions
122 Views
(@twindmon)
Posts: 1
New Member
Topic starter
 

in columns OP (modifiers) I need when the percentage to calculate 100% whenever is data is entered in column "O" but nothing in column "P".  it's working just fine when something is entered into column "O" and column "P". but again if nothing is entered into "O" but entered into "P" it does not return a zero and it should...HELP!!! please 

I've highlighted in yellow where it should calculate and green is the column to be calculated 

This is driving me bunkers and I swear it's going to be some simple fix I simply cannot figure out 🙁

 
Posted : 18/06/2019 9:40 am
Philip Treacy
(@philipt)
Posts: 1632
Member Admin
 

Hi Tiffany,

You're calculating a percentage by dividing three numbers

=IFERROR(1-((SUMIF($D$18:$D$67,E$1,$P$18:$P$67))/(SUMIF($D$18:$D$67,E$1,$O$18:$O$67)))/((SUMIF($D$18:$D$67,E$1,$P$18:$P$67))),"%")

and then subtracting the result from 1.  I'd just divide one by the other and set the cell format to Percentage.  And change the IFERROR to return 0 on a divide by zero

Try this

=IFERROR(((SUMIF($D$18:$D$67,E$1,$P$18:$P$67))/(SUMIF($D$18:$D$67,E$1,$O$18:$O$67))),0)

See modified file - attached.

Regards

Phil

 
Posted : 18/06/2019 5:46 pm
Share: