• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Between calculation for comparing two column ranges

P

ptwuk

New Member
I need to compare the percentatge value in column G with column F and then add up the toals of all cells that match the criteria.


The comparison is that the value in each cell in the range in column G is between 100% and 105% of the value in Column F.


I though if I insert another column E with the value of G + 105% for each cell I could then runthe folowing calcluation.


=SUMPRODUCT((G3:G117<=F3:F117)*(G3:G117>=E3:E117)*(A3:A117=1))


Hoever I got unexpected results, it returns just 1


The *(A3:A117=1)) part of the calculation is that it must have a 1 in that column to be included in the total.
 
Ptwuk

give the following a go

=SUMPRODUCT((((G3:G117)/(F3:F117))>1)*(((G3:G117)/(F3:F117))<1.05)*((A3:A117)=1))


If you want to include 100 and 105% change the < to <= etc

The above will count the number of rows where the criteria is true


To add something up add another set of data

=SUMPRODUCT((((G3:G117)/(F3:F117))>1)*(((G3:G117)/(F3:F117))<1.05)*((A3:A117)=1),(G3:G117))

Will add up the values in G3:G117 in rows where the preceding criteria is true


=SUMPRODUCT((((G3:G117)/(F3:F117))>1)*(((G3:G117)/(F3:F117))<1.05)*((A3:A117)=1),(G3:G117)*(F3*F117))

Will add up the Sumproduct of F3:F117*G3:G117 in rows where the preceding criteria is true
 
I get a #VALUE!error. Could it be because the data in each cell is a percentage and the cell is formatted as a percentage
 
This works with percentages for me

=SUMPRODUCT((((G2:G4)/(F2:F4))>100%)*(((G2:G4)/(F2:F4))<=105%)*(A2:A4))
 
Still getting the same error - I will have time later to look into it and will come back to you
 
Make sure your numbers are numbers and not text

ie: multiply them all by 1 in place, Type and copy value 1.0 somewhere, Paste Special Multiply across your data.
 
Tried what you suggested but still no change - I did check the cells and they were formatted as a percentage.


I don't mind sending the file if it helps to identify where I am going wrong.
 
Can you post it on a free file sharing site?

Make sure you anonymise the data
 
Sorry for the delay, I had to sort out a file sharing site. This is the link to the file:


http://www.keepandshare.com/doc/2056565/improvement-over-nov-2009-xls-july-9-2010-1-25-pm-169k?da=y
 
Sorry, I was away for the weekend - I have now changed this to Public. Can I also ask you if it is possible for me to get an automtatic email notification when anyone sends a reply or addds to a post? I have searched to see if the forum does this facitlity, but cannot find anything. I apologise if I am going off subject.
 
Ptwuk

Your problem is the "N/a" values in Columns F & G which are text values and hence stuff up the calc's hence the #VALUE! error, You Can't have a zero value either as you'll get a a #DIV/0! error


Solution: Seeing as the Sumproduct is only counting column A when Col F is between 100 + 105% of Col G can you just set both Col F & G to 100% and hence it won't trigger the count?
 
See what you mean - even left just blank it creates a problem. Is there an alternative formula or a way to not count - such as ISBLANK?


Also, not sure what you mean with the sumproduct - it should be counting column A if column F is between 100 and 105% of column E not G. Once I can get this cell right I can then use hte same principle to copy across to compare G,H, I etc to E
 
Ptwuk, In response to your

"Also, not sure what you mean with the sumproduct - it should be counting column A if column F is between 100 and 105% of column E not G. Once I can get this cell right I can then use hte same principle to copy across to compare G,H, I etc to E"


The formula is correct but it is referring to the wrong columns, you can adjust it to suit
 
I still don't see what you are getting at, whihc are the right columns? - can you make the changes and post the file back to a free sharing site so I can see what you mean?
 
Ptwuk

In cell F125 put the following

Code: 
=SUMPRODUCT((((F3:F117)/(E3:E117))>100%)*(((F3:F117)/(E3:E117))<=105%)*($A$3:$A$117))

Make sure all your values in Columns E..K don't have N/a or blanks

Copy across as relevent
 
Thanks for that - it does work perfectly if I remove the N/a's. But I need to have N/a or something that will not be counted (a zero is no good either)The reason I need this is that sometimes an area does not report their figures for one month, but do so for the next month - my solution was to enter Na or leave it blank. Is there any way I can accomodate this?
 
Solution found - I can leave blank cells provide I put a value in column E. I have put 0.0009% in place of N/a, that way the formula works fine. If you have blanks in both columns the formula adds the cell in Column G as it is 100% of the cell in Column E, which I'm sure is what you trying to tell me, and I couldn't grasp.
 
You must log in or register to reply here.
Back
Top