Compair Function? (1 Viewer)

[AC5FF]

Hey Everyone...

I have a spreadsheet with two columns of numbers. I would like to automate a side by side compairison of the numbers. For example, here is a what I am looking for

A...... B
0...... 3
2...... 2
3...... 4
1...... 0
1...... 2
0...... 8

Below this list I would like to have a cell tell me the percentage of times that column B is equal or greater than column A, only where column A is greater than 0. In the example the first/last lines wouldnt be counted since A=0. Basically for this example I would want a result of 75%

It's probably a simple formula I can use, but my brain just isn't catching up to it this morning...

Thx!

 

[Brianwarnock]

a simple way, I'm sure there must be a better one, is in Col C
enter in C1 and fill down

=IF(AND(B1>=A1,A1>0),1,0)

Then in C8 I put

=SUM(SUM(C1:C6)/C9)

and C9

=COUNTIF(A1:A6,">0")

Obviously you can choose columns and rows to suite you.

Brian

 

[AC5FF]

That would work... Problem I have is that I have 12 groups of these columns that I need to graph and then overlay the percentage line. The data is all imported from Access; I'd just have to have these 'sum' rows off to the right around column AA.

I got it to work; at the bottom of row B I had something like:
sum(Countif A1>0,(countif B1>=A1,1,0),0)+Countif A2>0,(countif B2>=A2,1,0),0)+Countif A3>0,(countif B3>=A3,1,0),0).....)
Or something similar.. Anyhow it did work, but the sum line was like 23 nested countif's! LOL Just took some playing around ..

 

[Brianwarnock]

I don't understand what you are doing, and it certainly is not what I answered, still if you are happy that is what counts.

Brian

 

[shades]

Anyhow it did work, but the sum line was like 23 nested countif's! LOL Just took some playing around ..

Well, you can nest up to seven IF statements in one formula. To go beyond that you would need a series of formulas in different cells (to eventually get to 49). I had produced one set that involved 13 IF statements in two formulas. It was a nightmare to set up, and even worse, to maintain, and then even worse than that... no one else could follow the logic!

A far better solution is to set up a table (on a hidden sheet with named ranges) and do a lookup. Simple formula, and the table approach allows you to change and adjust the criteria without ever changing the lookup formulas.
________
Grand Marquis

 

[AC5FF]

Shades:
Here's a quick cut/paste from my formula I'm using:

=SUM(IF(B2>0,IF(C2>=B2,1,0),0),IF(B3>0,IF(C3>=B3,1,0),0),IF(B4>0,IF(C4>=B4,1,0),0),IF(B5>0,IF(C5>=B5,1,0),0),IF(B6>0,IF(C6>=B6,1,0),0),IF(B7>0,IF(C7>=B7,1,0),0),IF(B8>0,IF(C8>=B8,1,0),0),IF(B9>0,IF(C9>=B9,1,0),0),IF(B10>0,IF(C10>=B10,1,0),0),IF(B11>0,IF(C11>=B11,1,0),0),IF(B12>0,IF(C12>=B12,1,0),0),IF(B13>0,IF(C13>=B13,1,0),0),IF(B14>0,IF(C14>=B14,1,0),0),IF(B15>0,IF(C15>=B15,1,0),0),IF(B16>0,IF(C16>=B16,1,0),0),IF(B17>0,IF(C17>=B17,1,0),0),IF(B18>0,IF(C18>=B18,1,0),0),IF(B19>0,IF(C19>=B19,1,0),0),IF(B20>0,IF(C20>=B20,1,0),0),IF(B21>0,IF(C21>=B21,1,0),0),IF(B22>0,IF(C22>=B22,1,0),0))

LoL.. Good luck following it! However, it works for what I need so I can't complain too loudly!

 

[Brianwarnock]

You prefered to type all that rather than the approach I suggested? And why not use the construct IF(AND(B1>=A1,A1>0),1,0)
You originally said that you wanted a %, where is that calculation?
Not sure why I bothered to reply.

Brian

 

[AC5FF]

Brian;
what you started put me on the right line of attack. This line sums the number of column B that is >= column A. That was the tough part; after that it is just like you mentioned; the sum / the count...

 

[shades]

Given that formula, you realize that you also have an upper limit on the number of characters in a formula (1,024 characters)? And how would you trouble shoot?

Well, if that twirls your hat...
________
Buy Vapor Genie

 

[AC5FF]

Charicter limit? I think i ran nto that with another spreadsheet I wrote years ago for tracking work production... Ended up having to use a hidden row to compute 1/2 of what i needed before hitting the final..

How to troubleshoot??? Since it works as I need it shouldn't need further troubleshooting If I need to edit/troubleshoot, no problem cuz i wrote it.. If I leave and someone else wants to change things up??? welll... Job Security dude.. Job Security... LOL

 

[shades]

If you are sure that it works in every possible situation, and you have tested...

Another issue is efficiency. I spend a fair amount of time looking for more efficient ways to accomplish tasks. That had led to job security, such that I trained other people to design and develop solutions for their projects. Given those circumstances, I found not only what worked, but what could be maintained with changing criteria/data, etc. by the person. And I would explain why one way would lead to further problems down the line, and another approach could avoid those problems.

It served me well. And that practice has served me well in over 40 years of work in many different career paths. Oh well....
________
VERMONT DISPENSARIES

 

[Brianwarnock]

When I first responded to this thread I thought that "this is a job for super function Sumproduct" but I am not terriffic with it and didn't have time to explore, however you might like to try

=SUMPRODUCT((B2:22>0)*(C2:22>=B2:B22))/SUMPRODUCT(--(B2:B22>0))

I suppose the denominator could be a Countif but it looks better like that.

I still do not know why you preferred
IF(B2>0,IF(C2>=B2,1,0),0) to IF(AND(C2>=B2,B2>0),1,0)


Brian

 

[qafself]

Why make life difficult!

Quite agree - this is a problem just made for SUMPRODUCT

From your original post the code below would work perfectly - format the cell as percentage

=SUMPRODUCT(--(A1:A6>0)*(B1:B6>=A1:A6))/SUMPRODUCT(--(A1:A6>0))

Adjust ranges to suit the data and remember that sumproduct can't have whole column refs e.g. A:A and the ranges must be the same.

Ed

 

[Brianwarnock]

Quite agree - this is a problem just made for SUMPRODUCT

From your original post the code below would work perfectly - format the cell as percentage

=SUMPRODUCT(--(A1:A6>0)*(B1:B6>=A1:A6))/SUMPRODUCT(--(A1:A6>0))

Adjust ranges to suit the data and remember that sumproduct can't have whole column refs e.g. A:A and the ranges must be the same.

Ed

The only difference that I can see between your formula and mine in the previous post is that you use the unary operator in the first Sumproduct, is that necessary? Isn't that only required in a single array situation ie no *.

Brian

 

[qafself]

Hi Brian,

I think you are technically right - I tend to whack it in as a matter of course and it does no harm.

I use sumproduct a lot - I 've developed a bit of a love affair with it - I presume sumifs in 2007 will make it redundant eventually

ED

 

[AC5FF]

I've never used SumProduct. I'll have to play around with that today some.
Curious though... What's the reason for the double minus signs??

 

[Brianwarnock]

Ed
sumproduct is far more versatile than Sumif, I wish that I had got to grips with it whilst working.

AC5FF
They are called unary operators, Sumproduct is for handling multi array situations , the Unary operators coerce the function to convert the TRUE/False result of a single conditional array into 1s and 0s .

I have attached a document that I obtained thanks to one of the gurus on here, I think it was Craigdolphin but it might have been Shades.

Brian

 

[qafself]

This is the best guidance around:

http://xldynamic.com/source/xld.SUMPRODUCT.html

I keep this on my favorites.

Also a very good site is

http://www.contextures.com/tiptech.html.

Hope they are of some use

Ed

 

[Brianwarnock]

Thanks ED, it looks like my document came from your first site.

Brian

 

[AC5FF]

Wow... Thanks Ed...
Definatly bookmarking these pages! Would have saved me 50-100 bucks had I seen these before now!! LOL... With everything available on the web it is a wonder why I keep buying the reference books! LOL