Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing visible values in two arrays
I'm having a problem in Excel for something that I thought would be
fairly easy to do, so I was wondering if anybody with more Excel experience than me could help. I have a table with four fields: Product, Sales Region, Target Sales, Actual Sales. I wanted a formula to calculate the proportion of all products which hit their target. I eventually did this by using {=SUM(IF(Dx:Dy=Cx:Cy,1,0))} and dividing by COUNT(Cx:Cy). However, I then decided that it might be useful to find a formula which did the same but only with visible cells, so that if I wanted to filter the table by Sales Region, I could easily get the corresponding hit rate. I tried using {=SUBTOTAL,(9,IF(IF(Dx:Dy=Cx:Cy,1,0)))} , but I get an error value. Does anyone know what I'm doing wrong? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing visible values in two arrays
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW($C$2:$C$20)-ROW($C$1),,1)),--($D$2:$D$2
0$C$2:$C$20)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ps.com... I'm having a problem in Excel for something that I thought would be fairly easy to do, so I was wondering if anybody with more Excel experience than me could help. I have a table with four fields: Product, Sales Region, Target Sales, Actual Sales. I wanted a formula to calculate the proportion of all products which hit their target. I eventually did this by using {=SUM(IF(Dx:Dy=Cx:Cy,1,0))} and dividing by COUNT(Cx:Cy). However, I then decided that it might be useful to find a formula which did the same but only with visible cells, so that if I wanted to filter the table by Sales Region, I could easily get the corresponding hit rate. I tried using {=SUBTOTAL,(9,IF(IF(Dx:Dy=Cx:Cy,1,0)))} , but I get an error value. Does anyone know what I'm doing wrong? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Arrays - declaration, adding values to arrays and calculation | Excel Programming | |||
Comparing to Arrays | Excel Programming | |||
Comparing to Arrays | Excel Programming | |||
Comparing to Arrays | Excel Programming | |||
Comparing to Arrays | Excel Programming |