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.
