Help with a formula

[Guest]

I was wondering if someone can help me with computation funcationality issue.

What I am trying to do is obtain a $ difference between 2 numbers with
these parameters based upon 3 different levels, 2 different cities, and 3
types of categories.

Computations trying to obtain:
If reference was city 1, cat 1 at L1
Obtain $ difference for that city 1
L1 L2 L3
cat 1 0 Diff $1-$1B $1-$1C
cat 2 $1-$2 $1-$2b $1-2c

The format of the data is:
CITY CATEGORY LEVEL AMOUNT
city 1 cat 1 L1 $1
city 1 cat 1 L2 $1B
city 1 cat 1 L3 $1C
city 1 cat 2 L1 $2
city 1 cat 2 L2 $2b
city 1 cat 2 L3 $2c
city 2 cat 1 L1 $12
city 2 cat 1 L2 $12b
city 2 cat 1 L3 $12c
city 2 cat 2 L1 $22
city 2 cat 2 L2 $22b
city 2 cat 2 L3 $22c

Thanks for your help

 

[Ken Johnson]

I was wondering if someone can help me with computation funcationality issue.

What I am trying to do is obtain a $ difference between 2 numbers with
these parameters based upon 3 different levels, 2 different cities, and 3
types of categories.

Computations trying to obtain:
If reference was city 1, cat 1 at L1
Obtain $ difference for that city 1
L1 L2 L3
cat 1 0 Diff $1-$1B $1-$1C
cat 2 $1-$2 $1-$2b $1-2c

The format of the data is:
CITY CATEGORY LEVEL AMOUNT
city 1 cat 1 L1 $1
city 1 cat 1 L2 $1B
city 1 cat 1 L3 $1C
city 1 cat 2 L1 $2
city 1 cat 2 L2 $2b
city 1 cat 2 L3 $2c
city 2 cat 1 L1 $12
city 2 cat 1 L2 $12b
city 2 cat 1 L3 $12c
city 2 cat 2 L1 $22
city 2 cat 2 L2 $22b
city 2 cat 2 L3 $22c

Thanks for your help

Say the reference city is in F1 and F1 is named RefCity, the reference
category is in F2 and F2 is named RefCat, the reference level is in F3
and F3 is named RefLevel.

Also, say that the column with the cities is named CITY, the column
with the categories is named CATEGORIES, the column with the levels is
named LEVELS and the column with the amounts is named AMOUNT.

With L1 in H1, L2 in I1, L3 in J1, cat 1 in G2 and cat 2 in G3, the
following formula in H2 filled across to H4 and down to row 3 should
give the amount differences...

=SUMPRODUCT((CITY=RefCity)*(CATEGORY=RefCat)*(LEVEL=RefLevel)*AMOUNT)-
SUMPRODUCT((CATEGORY=$G2)*(LEVEL=H$1)*AMOUNT)

Ken Johnson

 

[Ken Johnson]

Say the reference city is in F1 and F1 is named RefCity, the reference
category is in F2 and F2 is named RefCat, the reference level is in F3
and F3 is named RefLevel.

Also, say that the column with the cities is named CITY, the column
with the categories is named CATEGORIES, the column with the levels is
named LEVELS and the column with the amounts is named AMOUNT.

With L1 in H1, L2 in I1, L3 in J1, cat 1 in G2 and cat 2 in G3, the
following formula in H2 filled across to H4 and down to row 3 should
give the amount differences...

=SUMPRODUCT((CITY=RefCity)*(CATEGORY=RefCat)*(LEVEL=RefLevel)*AMOUNT)-
SUMPRODUCT((CATEGORY=$G2)*(LEVEL=H$1)*AMOUNT)

Ken Johnson

Oops,

I meant...

column with categories is named CATEGORY, and column with levels is
named LEVEL

Ken Johnson

 

[Guest]

Hello Ken, Thanks for the formula. I am a novice at this level. I have
some clarification on how how do I reference those columns i.e, RefCat.
Also, I am trying to understand your meaning of CITY=RefCity.

Thanks again for your feedback.

 

[Ken Johnson]

Hello Ken, Thanks for the formula. I am a novice at this level. I have
some clarification on how how do I reference those columns i.e, RefCat.
Also, I am trying to understand your meaning of CITY=RefCity.

Thanks again for your feedback.

Hi Pepper,

In your post you didn't indicate the addresses of any of the cells you
are working with so I defined named ranges for them.
If this has caused confusion the following example might help you come
up with a formula that doesn't used named ranges...

Say all the cells containing the cities, categories, levels and
amounts are...

cities A2:A100
categories B2:B100
levels C2:C100
amounts D2100

and that the city you are basing your current calculation on is
entered into F1...
the category you are basing your calculation on is entered into F2...
the level you are basing your calculation on is entered into F3

Your calculation table column heading are...

L1 in H1, L2 in I1 and L3 in J1

Your calculation table row headings are...

cat 1 in G2 and cat 2 in G3


then the formula in H2 would be...

=SUMPRODUCT(($A$2:$A$100=$F$1)*($B$2:$B$100=$F$2)*($C$2:$C$100=$F$3)*$D
$2:$D$100)-SUMPRODUCT(($B$2:$B$100=$G2)*($C$2:$C$100=H$1)*$D$2:$D$100)

This formula can be filled across to I2 and J2, then down to H3:J3.

Ken Johnson