need work out percentage

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Need to work out the percentage right for only positive counts

Need to work out the percentage right for only positive counts

resolvedResolved · Low Priority · Version 365

Sukhy has attended:
Excel Advanced course

Need to work out the percentage right for only positive counts

The Pivot is showing two positve numbers and 2 negative. The sum of positive numbers is 4 and the sum of negative numbers is 2. My percentage calculation is coming up as 33% but it needs to be 67%. Cannot figure out how to change this formula to only count the positive sums.

Formula used is=IFERROR(COUNTIF(S6:S50,">=1")/SUM(T6:T50),"NO DATA")
Columns S is the Positive/Negative data
Column T is the sum of Col S positive / negative numbers

First Col is S, Second is T. I need to have a formula which will end up say 4/6 to give me the % KPI but only using the positive numbers from Col S. If I add in -1 at the end it gives me -67%, not 67%.
5 2
-144 1
-237 1
8 2

RE: Need to work out the percentage right for only positive coun

Hello Sukhy,

Thank you for your question. If I understand correctly, this formula should work:

=SUMIF(T1:T4,2,T1:T4)/SUM(T1:T4)

The formula assumes positives carry a value of 2. It calculates the total for the 2 values only (positives), then expresses it as a percentage of the total of all 1s and 2s.

I hope this helps.

Kind regards
Marius Barnard
STL

RE: Need to work out the percentage right for only positive coun

Hi Marius, Thanks for getting back to me. Unfortunately, it doesnt give me the correct % still. For example I did it on the Aug data which should be 100% and it has given me 29%. I have noticed in your formula that col S is not included? This is the Column which will show the negative or positive number

RE: Need to work out the percentage right for only positive coun

Hi Sukhy,

Thanks for your reply. Am I correct to assume that all your August numbers are positive, hence the 100% you need to get?

For me to understand how to assist you more effectively, please could you clarify your process in the following from your first post:

"The sum of positive numbers is 4 and the sum of negative numbers is 2."

Do you assign a value of 2 to any positive number and 1 to any negative number?

Thanks
Marius

RE: Need to work out the percentage right for only positive coun

Hello, THis is quite hard to explain. THe Total number of SKUs is 7 for Aug and they were all postive numbers (so number of days completed before deadline). I do not assign a 1 or 2 to the formula. I only originally assigned 1 so it would count the number of days greater than 1




DATE FULLY Completed (All)
Month FULLY Completed Aug
Year FULLY Completed 22
Set to LIVE vs SIW & LEAD TIME (in Business days) Number of SKUs
7
5 3
26 2
30 1
22 1

RE: Need to work out the percentage right for only positive coun

Hi Sukhy,

For me to understand better, would it be possible to send a sample spreadsheet to the following email address:

info@stl-training.co.uk

Often, when we see the actual Excel version of the data, things make much more sense.

Thanks
Marius

Will be marked as resolved in 5 days

Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.


 

Excel tip:

Line breaks in a cell

You can control the line breaks for multiple-line headings or labels in your Microsoft Excel worksheet, just like you do in Microsoft Word. Here's how to do it.

Click the cell where you want the label or heading to appear.
Type the first line of information.
Press ALT+ENTER.

View all Excel hints and tips


Server loaded in 0.06 secs.