ANY SPECIAL ARRAY OR OTHER FORMULA
 

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

ANY SPECIAL ARRAY OR OTHER FORMULA
 

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" 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

ANY SPECIAL ARRAY OR OTHER FORMULA
 

WOW!!!!!!!!!!

YAHOOOO!!!!!!!!!!!!!!

THANXXXXXXXXX!!!!!!!!!!!!!!!!!

"T. Valko" wrote:

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" 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

ANY SPECIAL ARRAY OR OTHER FORMULA
 

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.

"T. Valko" wrote:

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" 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

ANY SPECIAL ARRAY OR OTHER FORMULA
 

Try this:

=SUMPRODUCT((MOD(COLUMN(A1:E1),2)=1)*A1:E1,(MOD(CO LUMN(B1:F1),2)=0)*B1:F1)/SUMPRODUCT(--(MOD(COLUMN(A1:E1),2)=1),--(B1:F1<0),A1:E1)

Biff

"FARAZ QURESHI" wrote 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.

"T. Valko" wrote:

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" 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

ANY SPECIAL ARRAY OR OTHER FORMULA
 

WOW!!!!!!!!!!

I CAN'T BELIEVE THIS!!!!!!!!!!!!!!

YAHOOOO!!!!!!!!!!!!!!

YOU ARE GREAT!!!!!!!!!!!!!!!!

YOU SURE HAVE MADE MY LIFE EASY!!!!!!!!!!!!!!!

THANXXXXXXXXX!!!!!!!!!!!!!!!!!


"T. Valko" wrote:

Try this:

=SUMPRODUCT((MOD(COLUMN(A1:E1),2)=1)*A1:E1,(MOD(CO LUMN(B1:F1),2)=0)*B1:F1)/SUMPRODUCT(--(MOD(COLUMN(A1:E1),2)=1),--(B1:F1<0),A1:E1)

Biff

"FARAZ QURESHI" wrote 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.

"T. Valko" wrote:

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" 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

ANY SPECIAL ARRAY OR OTHER FORMULA
 

Your'e welcome. Thanks for the feedback!

Biff

"FARAZ QURESHI" wrote in message
...
WOW!!!!!!!!!!

I CAN'T BELIEVE THIS!!!!!!!!!!!!!!

YAHOOOO!!!!!!!!!!!!!!

YOU ARE GREAT!!!!!!!!!!!!!!!!

YOU SURE HAVE MADE MY LIFE EASY!!!!!!!!!!!!!!!

THANXXXXXXXXX!!!!!!!!!!!!!!!!!


"T. Valko" wrote:

Try this:

=SUMPRODUCT((MOD(COLUMN(A1:E1),2)=1)*A1:E1,(MOD(CO LUMN(B1:F1),2)=0)*B1:F1)/SUMPRODUCT(--(MOD(COLUMN(A1:E1),2)=1),--(B1:F1<0),A1:E1)

Biff

"FARAZ QURESHI" wrote 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.

"T. Valko" wrote:

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" 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

ANY SPECIAL ARRAY OR OTHER FORMULA
 

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???

"T. Valko" wrote:

Your'e welcome. Thanks for the feedback!

Biff

"FARAZ QURESHI" wrote in message
...
WOW!!!!!!!!!!

I CAN'T BELIEVE THIS!!!!!!!!!!!!!!

YAHOOOO!!!!!!!!!!!!!!

YOU ARE GREAT!!!!!!!!!!!!!!!!

YOU SURE HAVE MADE MY LIFE EASY!!!!!!!!!!!!!!!

THANXXXXXXXXX!!!!!!!!!!!!!!!!!


"T. Valko" wrote:

Try this:

=SUMPRODUCT((MOD(COLUMN(A1:E1),2)=1)*A1:E1,(MOD(CO LUMN(B1:F1),2)=0)*B1:F1)/SUMPRODUCT(--(MOD(COLUMN(A1:E1),2)=1),--(B1:F1<0),A1:E1)

Biff

"FARAZ QURESHI" wrote 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.

"T. Valko" wrote:

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" 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

ANY SPECIAL ARRAY OR OTHER FORMULA
 

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

"FARAZ QURESHI" wrote in 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???

"T. Valko" wrote:

Your'e welcome. Thanks for the feedback!

Biff

"FARAZ QURESHI" wrote in message
...
WOW!!!!!!!!!!

I CAN'T BELIEVE THIS!!!!!!!!!!!!!!

YAHOOOO!!!!!!!!!!!!!!

YOU ARE GREAT!!!!!!!!!!!!!!!!

YOU SURE HAVE MADE MY LIFE EASY!!!!!!!!!!!!!!!

THANXXXXXXXXX!!!!!!!!!!!!!!!!!


"T. Valko" wrote:

Try this:

=SUMPRODUCT((MOD(COLUMN(A1:E1),2)=1)*A1:E1,(MOD(CO LUMN(B1:F1),2)=0)*B1:F1)/SUMPRODUCT(--(MOD(COLUMN(A1:E1),2)=1),--(B1:F1<0),A1:E1)

Biff

"FARAZ QURESHI" wrote 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.

"T. Valko" wrote:

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" 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

ANY SPECIAL ARRAY OR OTHER FORMULA
 

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

"T. Valko" wrote in message
...
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

"FARAZ QURESHI" wrote in 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???

"T. Valko" wrote:

Your'e welcome. Thanks for the feedback!

Biff

"FARAZ QURESHI" wrote in
message
...
WOW!!!!!!!!!!

I CAN'T BELIEVE THIS!!!!!!!!!!!!!!

YAHOOOO!!!!!!!!!!!!!!

YOU ARE GREAT!!!!!!!!!!!!!!!!

YOU SURE HAVE MADE MY LIFE EASY!!!!!!!!!!!!!!!

THANXXXXXXXXX!!!!!!!!!!!!!!!!!


"T. Valko" wrote:

Try this:

=SUMPRODUCT((MOD(COLUMN(A1:E1),2)=1)*A1:E1,(MOD(CO LUMN(B1:F1),2)=0)*B1:F1)/SUMPRODUCT(--(MOD(COLUMN(A1:E1),2)=1),--(B1:F1<0),A1:E1)

Biff

"FARAZ QURESHI" wrote 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.

"T. Valko" wrote:

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" 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

ANY SPECIAL ARRAY OR OTHER FORMULA
 

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

"T. Valko" wrote in message
...
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

"T. Valko" wrote in message
...
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

"FARAZ QURESHI" wrote in 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???

"T. Valko" wrote:

Your'e welcome. Thanks for the feedback!

Biff

"FARAZ QURESHI" wrote in
message
...
WOW!!!!!!!!!!

I CAN'T BELIEVE THIS!!!!!!!!!!!!!!

YAHOOOO!!!!!!!!!!!!!!

YOU ARE GREAT!!!!!!!!!!!!!!!!

YOU SURE HAVE MADE MY LIFE EASY!!!!!!!!!!!!!!!

THANXXXXXXXXX!!!!!!!!!!!!!!!!!


"T. Valko" wrote:

Try this:

=SUMPRODUCT((MOD(COLUMN(A1:E1),2)=1)*A1:E1,(MOD(CO LUMN(B1:F1),2)=0)*B1:F1)/SUMPRODUCT(--(MOD(COLUMN(A1:E1),2)=1),--(B1:F1<0),A1:E1)

Biff

"FARAZ QURESHI" wrote 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.

"T. Valko" wrote:

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" 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

ANY SPECIAL ARRAY OR OTHER FORMULA
 

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

"T. Valko" wrote:

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

"T. Valko" wrote in message
...
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

"T. Valko" wrote in message
...
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

"FARAZ QURESHI" wrote in 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???

"T. Valko" wrote:

Your'e welcome. Thanks for the feedback!

Biff

"FARAZ QURESHI" wrote in
message
...
WOW!!!!!!!!!!

I CAN'T BELIEVE THIS!!!!!!!!!!!!!!

YAHOOOO!!!!!!!!!!!!!!

YOU ARE GREAT!!!!!!!!!!!!!!!!

YOU SURE HAVE MADE MY LIFE EASY!!!!!!!!!!!!!!!

THANXXXXXXXXX!!!!!!!!!!!!!!!!!


"T. Valko" wrote:

Try this:

=SUMPRODUCT((MOD(COLUMN(A1:E1),2)=1)*A1:E1,(MOD(CO LUMN(B1:F1),2)=0)*B1:F1)/SUMPRODUCT(--(MOD(COLUMN(A1:E1),2)=1),--(B1:F1<0),A1:E1)

Biff

"FARAZ QURESHI" wrote 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.

"T. Valko" wrote:

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" 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

ANY SPECIAL ARRAY OR OTHER FORMULA
 

You're welcome. Good luck!

Biff

"FARAZ QURESHI" wrote in message
...
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

"T. Valko" wrote:

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

"T. Valko" wrote in message
...
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

"T. Valko" wrote in message
...
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

"FARAZ QURESHI" wrote in
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???

"T. Valko" wrote:

Your'e welcome. Thanks for the feedback!

Biff

"FARAZ QURESHI" wrote in
message
...
WOW!!!!!!!!!!

I CAN'T BELIEVE THIS!!!!!!!!!!!!!!

YAHOOOO!!!!!!!!!!!!!!

YOU ARE GREAT!!!!!!!!!!!!!!!!

YOU SURE HAVE MADE MY LIFE EASY!!!!!!!!!!!!!!!

THANXXXXXXXXX!!!!!!!!!!!!!!!!!


"T. Valko" wrote:

Try this:

=SUMPRODUCT((MOD(COLUMN(A1:E1),2)=1)*A1:E1,(MOD(CO LUMN(B1:F1),2)=0)*B1:F1)/SUMPRODUCT(--(MOD(COLUMN(A1:E1),2)=1),--(B1:F1<0),A1:E1)

Biff

"FARAZ QURESHI" wrote 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.

"T. Valko" wrote:

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" 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

ANY SPECIAL ARRAY OR OTHER FORMULA
 

Hi Biff,

Hope everything's well!

Got a new assignment and again i am in trouble.

The columns to be multiplied for weighted average are neither in Odd or Even
sequence for Modulus Function to apply.

What was the logic of using "--"?

Can u tell me how to calculate a same weighted average rate if:

Column A: Branch Name
Column B: Units
Column C: Rate

Then
Column D: Branch Name
Column E: Units
Column F: Rate

Then Again
Column G: Branch Name
Column H: Units
Column I: Rate

Thanx again pal!

FARAZ

"T. Valko" wrote:

You're welcome. Good luck!

Biff

"FARAZ QURESHI" wrote in message
...
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

"T. Valko" wrote:

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

"T. Valko" wrote in message
...
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

"T. Valko" wrote in message
...
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

"FARAZ QURESHI" wrote in
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???

"T. Valko" wrote:

Your'e welcome. Thanks for the feedback!

Biff

"FARAZ QURESHI" wrote in
message
...
WOW!!!!!!!!!!

I CAN'T BELIEVE THIS!!!!!!!!!!!!!!

YAHOOOO!!!!!!!!!!!!!!

YOU ARE GREAT!!!!!!!!!!!!!!!!

YOU SURE HAVE MADE MY LIFE EASY!!!!!!!!!!!!!!!

THANXXXXXXXXX!!!!!!!!!!!!!!!!!


"T. Valko" wrote:

Try this:

=SUMPRODUCT((MOD(COLUMN(A1:E1),2)=1)*A1:E1,(MOD(CO LUMN(B1:F1),2)=0)*B1:F1)/SUMPRODUCT(--(MOD(COLUMN(A1:E1),2)=1),--(B1:F1<0),A1:E1)

Biff

"FARAZ QURESHI" wrote 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.

"T. Valko" wrote:

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" 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

ANY SPECIAL ARRAY OR OTHER FORMULA
 

Here's an updated file:

Faraz(2).xls

http://cjoint.com/?exhcHzxt1H

Biff

"FARAZ QURESHI" wrote in message
...
Hi Biff,

Hope everything's well!

Got a new assignment and again i am in trouble.

The columns to be multiplied for weighted average are neither in Odd or
Even
sequence for Modulus Function to apply.

What was the logic of using "--"?

Can u tell me how to calculate a same weighted average rate if:

Column A: Branch Name
Column B: Units
Column C: Rate

Then
Column D: Branch Name
Column E: Units
Column F: Rate

Then Again
Column G: Branch Name
Column H: Units
Column I: Rate

Thanx again pal!

FARAZ

"T. Valko" wrote:

You're welcome. Good luck!

Biff

"FARAZ QURESHI" wrote in message
...
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

"T. Valko" wrote:

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

"T. Valko" wrote in message
...
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

"T. Valko" wrote in message
...
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

"FARAZ QURESHI" wrote in
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???

"T. Valko" wrote:

Your'e welcome. Thanks for the feedback!

Biff

"FARAZ QURESHI" wrote in
message
...
WOW!!!!!!!!!!

I CAN'T BELIEVE THIS!!!!!!!!!!!!!!

YAHOOOO!!!!!!!!!!!!!!

YOU ARE GREAT!!!!!!!!!!!!!!!!

YOU SURE HAVE MADE MY LIFE EASY!!!!!!!!!!!!!!!

THANXXXXXXXXX!!!!!!!!!!!!!!!!!


"T. Valko" wrote:

Try this:

=SUMPRODUCT((MOD(COLUMN(A1:E1),2)=1)*A1:E1,(MOD(CO LUMN(B1:F1),2)=0)*B1:F1)/SUMPRODUCT(--(MOD(COLUMN(A1:E1),2)=1),--(B1:F1<0),A1:E1)

Biff

"FARAZ QURESHI" wrote
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.

"T. Valko" wrote:

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"
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