Re: Help with a formula
- From: Ken Johnson <KenCJohnson@xxxxxxxxx>
- Date: Tue, 05 Jun 2007 14:35:07 -0700
On Jun 6, 3:31 am, Pepper <Pep...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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" wrote:
On May 26, 6:42 am, Pepper <Pep...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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
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 D2:D100
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
.
- Prev by Date: Re: how can i round in excel to the nearest thousand
- Next by Date: Re: how can i round in excel to the nearest thousand
- Previous by thread: Re: Return blank cell if value you vlookup is not on pivot
- Next by thread: day vs time format ??
- Index(es):
Relevant Pages
|
