98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum 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
Resolved · 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.
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Line breaks in a cellYou 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. |