Adding Percentages Together for Total

Chloe Rose

Hi!

I'm trying to figure out a way to add the percentages from the Column labeled "Capacity" with a function and have them automatically tally together in the "Total Percentage" column. The percent value is being calculated with the Nested IF formula that I have shown.

The point is that I will eventually add these columns to a larger sheet with all active projects. We are trying to find a way through smartsheets to automatically calculate how much capacity each individual Project Manager is operating under and visualize all the data in a dashboard. It needs to be automated as much as possible otherwise it needs to be manually updated and it defeats the whole purpose.

In a perfect smartsheet world we want to have our sheet with all active projects and the columns PME, PCR, Capacity and total percentage. Each project manager will have their projects sorted by their email address and the total percentage will be calculated somehow. Then using sheet summary take all the individual project manager data with their total percentage of capacity and visualize it with a dashboard. Sort of a more involved/complicated way of doing resource management.

I hope I explained this in a way that makes sense!

Thanks for any help provided.

Genevieve P.

Hi Chloe,

My apologies, I should have been more clear! Adding the quotation marks around the decimals treats it as text again, so it won't format properly. So instead of ".5", try 0.5, no quotes.

Try this, with just the numbers:

=IF(AND(PME@row = "easy", PCR@row = "intermediate"), 0.5, (IF(AND(PME@row = "easy", PCR@row = "easy"), 0.25, IF(AND(PME@row = "easy", PCR@row = "advanced"), 1, IF(AND(PME@row = "intermediate", PCR@row = "easy"), .1, (IF(AND(PME@row = "intermediate", PCR@row = "intermediate"), 0.2, IF(AND(PME@row = "intermediate", PCR@row = "advanced"), 0.5, IF(AND(PME@row = "advanced", PCR@row = "easy"), 0.1, (IF(AND(PME@row = "advanced", PCR@row = "intermediate"), 0.15, IF(AND(PME@row = "advanced", PCR@row = "advanced"), 0.25, IF(AND(PME@row = "experts only", PCR@row = "easy"), 0.1, (IF(AND(PME@row = "experts only", PCR@row = "intermediate"), 0.15, IF(AND(PME@row = "experts only", PCR@row = "advanced"), 0.25, IF(AND(PME@row = "experts only", PCR@row = "experts only"), 0.25, 0)))))))))))))))))

You can tell if it's working because the numbers should appear on the right of the cell. Text appears on the left, whereas numerical values appear to the right.

Let me know if this works!

Genevieve

Genevieve P.

Hi @Chloe Rose - 8PCC

I think where you may be having issues is that the percent is being returned as text, since in your Nested If you have "50%" written in the formula. Percents in formulas need to be written as decimals in order to be identified as numerical (see here), then the Capacity column would be formatted to add in the % sign... ex:

=IF(AND(PME@row = "easy", PCR@row = "intermediate"), 0.5, IF..... etc

You could either re-write this formula with decimals, or you could use a formula in that Total Percentage column that looks at the value in the Capacity column in its row and return the numeric version:

=IF(Capacity@row = "100%", 1, VALUE(LEFT((Capacity@row), 2)) / 100)

Since the LEFT function is only grabbing the 2 numbers on the left of the cell, if the value is actually 100 it would read it as 10... this is why I added the additional IF statement rule at the beginning. This formula will return a decimal in the Total Percentage column. You can then format the column as a % and it will adjust this.

Then at the top of this column you could use a SUM function and it will work on the numbers, now! You could also do SUMIF statement if you only wanted to sum the percent based on one specific Project Manager, referencing this helper column so it has numerical data to sum. Let me know if this makes sense, or if I've misunderstood your question!

Cheers,

Genevieve

Chloe Rose

Hi Genevive,

Thanks for your reply. I have no idea why this is so hard for me! But here's what I did trying to follow the directions you gave:

Changed the NestedIF to decimals:

=IF(AND(PME@row = "easy", PCR@row = "intermediate"), ".5", (IF(AND(PME@row = "easy", PCR@row = "easy"), ".25", IF(AND(PME@row = "easy", PCR@row = "advanced"), "1", IF(AND(PME@row = "intermediate", PCR@row = "easy"), ".1", (IF(AND(PME@row = "intermediate", PCR@row = "intermediate"), ".2", IF(AND(PME@row = "intermediate", PCR@row = "advanced"), ".5", IF(AND(PME@row = "advanced", PCR@row = "easy"), ".1", (IF(AND(PME@row = "advanced", PCR@row = "intermediate"), ".15", IF(AND(PME@row = "advanced", PCR@row = "advanced"), ".25", IF(AND(PME@row = "experts only", PCR@row = "easy"), ".1", (IF(AND(PME@row = "experts only", PCR@row = "intermediate"), ".15", IF(AND(PME@row = "experts only", PCR@row = "advanced"), ".25", IF(AND(PME@row = "experts only", PCR@row = "experts only"), ".25", "0")))))))))))))))))

I've tried to make the Capacity column format as percent by highlighting the entire column and hitting the % symbol on the task bar. It doesn't change anything. The numbers still show up as decimals.

Then I tried to do the SUMIF again, it still shows up as 0.

Genevieve P.

Hi Chloe,

My apologies, I should have been more clear! Adding the quotation marks around the decimals treats it as text again, so it won't format properly. So instead of ".5", try 0.5, no quotes.

Try this, with just the numbers:

=IF(AND(PME@row = "easy", PCR@row = "intermediate"), 0.5, (IF(AND(PME@row = "easy", PCR@row = "easy"), 0.25, IF(AND(PME@row = "easy", PCR@row = "advanced"), 1, IF(AND(PME@row = "intermediate", PCR@row = "easy"), .1, (IF(AND(PME@row = "intermediate", PCR@row = "intermediate"), 0.2, IF(AND(PME@row = "intermediate", PCR@row = "advanced"), 0.5, IF(AND(PME@row = "advanced", PCR@row = "easy"), 0.1, (IF(AND(PME@row = "advanced", PCR@row = "intermediate"), 0.15, IF(AND(PME@row = "advanced", PCR@row = "advanced"), 0.25, IF(AND(PME@row = "experts only", PCR@row = "easy"), 0.1, (IF(AND(PME@row = "experts only", PCR@row = "intermediate"), 0.15, IF(AND(PME@row = "experts only", PCR@row = "advanced"), 0.25, IF(AND(PME@row = "experts only", PCR@row = "experts only"), 0.25, 0)))))))))))))))))

You can tell if it's working because the numbers should appear on the right of the cell. Text appears on the left, whereas numerical values appear to the right.

Let me know if this works!

Genevieve

Chloe Rose

Oh my goodness! It worked!

Thank you so much for your help!

Genevieve P.

So glad to hear that! No problem at all 🙂