HEY BIFF
Man u r great
No doubt the sheet 1 was great, but the problem, being solved by you,
was
that I have a 2 dimensional data. i.e. different products' units for
different branches.
Can't express my gratitude, pal.
U have filled up my Sunday with real happiness, having myself present
as a
genious in my organization and excel champion.
Sure do owe it all to u.
Thanx again.
A Pakistani Pal,
FARAZ QURESHI
:
Check out this sample file:
Faraz.xls 14kb
http://cjoint.com/?epdeKgILeW
Sheet1 is how I would setup your data. Sheet2 is how you have your
data
setup.
Notice how much simpler the formulas are on Sheet1 compared to those
on
Sheet2.
Biff
If I were you I would redesign this layout! I would put the data
vertically down the columns instead of horizontally across rows.
The formulas would be *much* simpler!
Biff
I had a feeling you weren't done! <g>
If I were you I would redesign this layout! I would put the data
vertically down the columns instead of horizontally across rows.
I don't have time to look at this tonight (2:00AM where I'm at).
I'll
look at it tomorrow.
Biff
message
You are GREAT Mr. Valko,
By the way, what if the data is spread like in the following
format:
Column A: Branch Name
Column B: Units
Column C: Rate
Column D: Manager
Again
Column E: Branch Name
Column F: Units
Column G: Rate
Column H: Manager
And again
Column I: Branch Name
Column J: Units
Column K: Rate
Column L: Manager
Would there be any method to consider only columns B, C, F, G, J &
K
to
be
considered for this purpose. Can the MOD function be adjusted
enough
to
do
this as well???
:
Your'e welcome. Thanks for the feedback!
Biff
message
WOW!!!!!!!!!!
I CAN'T BELIEVE THIS!!!!!!!!!!!!!!
YAHOOOO!!!!!!!!!!!!!!
YOU ARE GREAT!!!!!!!!!!!!!!!!
YOU SURE HAVE MADE MY LIFE EASY!!!!!!!!!!!!!!!
THANXXXXXXXXX!!!!!!!!!!!!!!!!!
:
Try this:
=SUMPRODUCT((MOD(COLUMN(A1:E1),2)=1)*A1:E1,(MOD(COLUMN(B1:F1),2)=0)*B1:F1)/SUMPRODUCT(--(MOD(COLUMN(A1:E1),2)=1),--(B1:F1<>0),A1:E1)
Biff
in
message
Dear Mr. Valko,
I sure am thankful for your all your help. That was an
excellent
approach.
However, I have another question!
Suppose, I want to compute the weighted average rate. In
other
words
yours
recommended
[=SUMPRODUCT(((MOD(COLUMN(A1:E1),2)=1)*A1:E1)*((MOD(COLUMN(B1:F1),2)=0)*B1:F1))]
being divided by (A1+C1+E1). Any other similar special
formula
to
sum-up
the
components only if rate is availabale???
e.g. if D1 is blank or equal to zero corresponding number of
units
in
C1
is
not considered in calculation of divisor for accurate
weighted
average
rate.
:
Try this:
=SUMPRODUCT(((MOD(COLUMN(A1:E1),2)=1)*A1:E1)*((MOD(COLUMN(B1:F1),2)=0)*B1:F1))
Note how the ranges are staggered.
Biff
"FARAZ QURESHI" <
[email protected]>
wrote
in
message
I have my data laid out in the format as follows:
A B C D E F
90 10 200 11 450 8
In the above example columns A, C & E reflect the number
of
units
while
columns B, D & F declare the selling rate per unit for
the
different
products.
I need the amount of Total Sales in Column G and
desparately
need an
array
type of special formula to compute (A1*B1+C1*D1+E1*F1)
easily
as the
Actual
Data comprises of around 40 products containing around 40
different
rates.
Help is desparately required and shall be highly obliged.
Thanx
