Find amount in row.
 

Hello All:

I would like to know how can I find out the folowing. I have a chart like
this:

Type Company 1 Company 2 Company 3
Product
Plastic 3.69 0.79 0.89
Metal 0.99 2.57 1.99
Wood 1.69 0.89 0.99

If I type in a cell A1 Plastic, then then cell A2 should tell me the highest
price found in the row for plastic.

I also want to be able to type plastic in A1 and then in B1 wood and in cell
A2 should calculate the total highest price for both.

Thanks in advance for any help.

Find amount in row.
 

Assuming you have data in A1:J10 with first row with headers the below will
return the maximum value of the item..Companies from Col B to Col J.

A11 = Plastic

=MAX(INDIRECT("B" & MATCH(A11,A1:A10,0) & ":J" & MATCH(A11,A1:A10,0)))


If this post helps click Yes
---------------
Jacob Skaria


"art" wrote:

Hello All:

I would like to know how can I find out the folowing. I have a chart like
this:

Type Company 1 Company 2 Company 3
Product
Plastic 3.69 0.79 0.89
Metal 0.99 2.57 1.99
Wood 1.69 0.89 0.99

If I type in a cell A1 Plastic, then then cell A2 should tell me the highest
price found in the row for plastic.

I also want to be able to type plastic in A1 and then in B1 wood and in cell
A2 should calculate the total highest price for both.

Thanks in advance for any help.

Find amount in row.
 

Assume this data is in the range F2:I4 -

Plastic, 3.69, 0.79, 0.89
Metal, 0.99, 2.57, 1.99
Wood, 1.69, 0.89, 0.99

A1 = plastic
B1 = wood

Enter this formula in A2 and copy across to B2:

=MAX(INDEX($G2:$I4,MATCH(A1,$F2:$F4,0),0))


--
Biff
Microsoft Excel MVP


"art" wrote in message
...
Hello All:

I would like to know how can I find out the folowing. I have a chart like
this:

Type Company 1 Company 2 Company 3
Product
Plastic 3.69 0.79 0.89
Metal 0.99 2.57 1.99
Wood 1.69 0.89 0.99

If I type in a cell A1 Plastic, then then cell A2 should tell me the
highest
price found in the row for plastic.

I also want to be able to type plastic in A1 and then in B1 wood and in
cell
A2 should calculate the total highest price for both.

Thanks in advance for any help.

Find amount in row.
 

Something is wrong with this formula, it found the wrong price.

"Jacob Skaria" wrote:

Assuming you have data in A1:J10 with first row with headers the below will
return the maximum value of the item..Companies from Col B to Col J.

A11 = Plastic

=MAX(INDIRECT("B" & MATCH(A11,A1:A10,0) & ":J" & MATCH(A11,A1:A10,0)))


If this post helps click Yes
---------------
Jacob Skaria


"art" wrote:

Hello All:

I would like to know how can I find out the folowing. I have a chart like
this:

Type Company 1 Company 2 Company 3
Product
Plastic 3.69 0.79 0.89
Metal 0.99 2.57 1.99
Wood 1.69 0.89 0.99

If I type in a cell A1 Plastic, then then cell A2 should tell me the highest
price found in the row for plastic.

I also want to be able to type plastic in A1 and then in B1 wood and in cell
A2 should calculate the total highest price for both.

Thanks in advance for any help.

Find amount in row.
 

Many Thanks. However, can I use a formula that will calculate both items in
one cell and give me the total. So if I enter in A1 Plastic and in A2 Wood,
it should give me the total?

Also, what would I need to change to to give one less then max?

Thanks.

"T. Valko" wrote:

Assume this data is in the range F2:I4 -

Plastic, 3.69, 0.79, 0.89
Metal, 0.99, 2.57, 1.99
Wood, 1.69, 0.89, 0.99

A1 = plastic
B1 = wood

Enter this formula in A2 and copy across to B2:

=MAX(INDEX($G2:$I4,MATCH(A1,$F2:$F4,0),0))


--
Biff
Microsoft Excel MVP


"art" wrote in message
...
Hello All:

I would like to know how can I find out the folowing. I have a chart like
this:

Type Company 1 Company 2 Company 3
Product
Plastic 3.69 0.79 0.89
Metal 0.99 2.57 1.99
Wood 1.69 0.89 0.99

If I type in a cell A1 Plastic, then then cell A2 should tell me the
highest
price found in the row for plastic.

I also want to be able to type plastic in A1 and then in B1 wood and in
cell
A2 should calculate the total highest price for both.

Thanks in advance for any help.

Find amount in row.
 

For finding the second largest you can use the formula LARGE() function
instead of MAX (). = LARGE(A1:A10,2) will give you the second largest..

I tried the formula and it works with the data in A1 : E4; something like
the below
Item Comp 1 Comp 2 Comp 3 Comp 4
Plastic 3 2 1 4
Wood 2 5 6 1
Item 3 3 2 2 1

In A11 = Wood
B11 = MAX(INDIRECT("B" & MATCH(A11,$A$1:$A$10,0) & ":J" &
MATCH(A11,$A$1:$A$10,0)))

However using INDEX is the easiest one..with out repeating MAX
For getting totals of both for example A11=Wood A12=Plastic you can add the
same formula with reference to A12.

=MAX(INDEX($B$1:$E$4,MATCH(A11,$A$1:$A$4,0),0))+MA X(INDEX($B$1:$E$4,MATCH(A12,$A$1:$A$4,0),0))


If this post helps click Yes
---------------
Jacob Skaria


"art" wrote:

Something is wrong with this formula, it found the wrong price.

"Jacob Skaria" wrote:

Assuming you have data in A1:J10 with first row with headers the below will
return the maximum value of the item..Companies from Col B to Col J.

A11 = Plastic

=MAX(INDIRECT("B" & MATCH(A11,A1:A10,0) & ":J" & MATCH(A11,A1:A10,0)))


If this post helps click Yes
---------------
Jacob Skaria


"art" wrote:

Hello All:

I would like to know how can I find out the folowing. I have a chart like
this:

Type Company 1 Company 2 Company 3
Product
Plastic 3.69 0.79 0.89
Metal 0.99 2.57 1.99
Wood 1.69 0.89 0.99

If I type in a cell A1 Plastic, then then cell A2 should tell me the highest
price found in the row for plastic.

I also want to be able to type plastic in A1 and then in B1 wood and in cell
A2 should calculate the total highest price for both.

Thanks in advance for any help.

amount in row
 

Thanks. Any other way to calculate the total other than entering the whole
formula a few times with +? like an array?

Find amount in row.
 

Try this:
=MAX((F2:F4=A1)*G2:I4)+MAX((F2:F4=B1)*G2:I4)

ctrl+shift+enter, not just enter



"art" wrote:

Many Thanks. However, can I use a formula that will calculate both items in
one cell and give me the total. So if I enter in A1 Plastic and in A2 Wood,
it should give me the total?

Also, what would I need to change to to give one less then max?

Thanks.

"T. Valko" wrote:

Assume this data is in the range F2:I4 -

Plastic, 3.69, 0.79, 0.89
Metal, 0.99, 2.57, 1.99
Wood, 1.69, 0.89, 0.99

A1 = plastic
B1 = wood

Enter this formula in A2 and copy across to B2:

=MAX(INDEX($G2:$I4,MATCH(A1,$F2:$F4,0),0))


--
Biff
Microsoft Excel MVP


"art" wrote in message
...
Hello All:

I would like to know how can I find out the folowing. I have a chart like
this:

Type Company 1 Company 2 Company 3
Product
Plastic 3.69 0.79 0.89
Metal 0.99 2.57 1.99
Wood 1.69 0.89 0.99

If I type in a cell A1 Plastic, then then cell A2 should tell me the
highest
price found in the row for plastic.

I also want to be able to type plastic in A1 and then in B1 wood and in
cell
A2 should calculate the total highest price for both.

Thanks in advance for any help.

Find amount in row.
 

Thanks. But I really want to be able to use a formula that will calculate the
total without having to enter "+" and the entire formula for each item I add.
I will have many companies and items, and if I want a total of the whole
thing, I'll have to enter the whole formula many times adding everything
together.

Is there no way that I can total the whole thing using an array formula or
something else, and avoide having to enter the entire fomrula for each item?

Thanks for any help.

"Teethless mama" wrote:

Try this:
=MAX((F2:F4=A1)*G2:I4)+MAX((F2:F4=B1)*G2:I4)

ctrl+shift+enter, not just enter



"art" wrote:

Many Thanks. However, can I use a formula that will calculate both items in
one cell and give me the total. So if I enter in A1 Plastic and in A2 Wood,
it should give me the total?

Also, what would I need to change to to give one less then max?

Thanks.

"T. Valko" wrote:

Assume this data is in the range F2:I4 -

Plastic, 3.69, 0.79, 0.89
Metal, 0.99, 2.57, 1.99
Wood, 1.69, 0.89, 0.99

A1 = plastic
B1 = wood

Enter this formula in A2 and copy across to B2:

=MAX(INDEX($G2:$I4,MATCH(A1,$F2:$F4,0),0))


--
Biff
Microsoft Excel MVP


"art" wrote in message
...
Hello All:

I would like to know how can I find out the folowing. I have a chart like
this:

Type Company 1 Company 2 Company 3
Product
Plastic 3.69 0.79 0.89
Metal 0.99 2.57 1.99
Wood 1.69 0.89 0.99

If I type in a cell A1 Plastic, then then cell A2 should tell me the
highest
price found in the row for plastic.

I also want to be able to type plastic in A1 and then in B1 wood and in
cell
A2 should calculate the total highest price for both.

Thanks in advance for any help.

Find amount in row.
 

Assume this data is in the range F2:I4 -

Plastic, 3.69, 0.79, 0.89
Metal, 0.99, 2.57, 1.99
Wood, 1.69, 0.89, 0.99

A1 = plastic
B1 = wood

=SUMPRODUCT(SUBTOTAL(4,OFFSET(G2:I4,ROW(G2:I4)-ROW(G2),0,1)),--(ISNUMBER(MATCH(F2:F4,A1:B1,0))))

Also, what would I need to change to
to give one less then max?

You're really pressing your luck!


--
Biff
Microsoft Excel MVP


"art" wrote in message
...
Thanks. But I really want to be able to use a formula that will calculate
the
total without having to enter "+" and the entire formula for each item I
add.
I will have many companies and items, and if I want a total of the whole
thing, I'll have to enter the whole formula many times adding everything
together.

Is there no way that I can total the whole thing using an array formula or
something else, and avoide having to enter the entire fomrula for each
item?

Thanks for any help.

"Teethless mama" wrote:

Try this:
=MAX((F2:F4=A1)*G2:I4)+MAX((F2:F4=B1)*G2:I4)

ctrl+shift+enter, not just enter



"art" wrote:

Many Thanks. However, can I use a formula that will calculate both
items in
one cell and give me the total. So if I enter in A1 Plastic and in A2
Wood,
it should give me the total?

Also, what would I need to change to to give one less then max?

Thanks.

"T. Valko" wrote:

Assume this data is in the range F2:I4 -

Plastic, 3.69, 0.79, 0.89
Metal, 0.99, 2.57, 1.99
Wood, 1.69, 0.89, 0.99

A1 = plastic
B1 = wood

Enter this formula in A2 and copy across to B2:

=MAX(INDEX($G2:$I4,MATCH(A1,$F2:$F4,0),0))


--
Biff
Microsoft Excel MVP


"art" wrote in message
...
Hello All:

I would like to know how can I find out the folowing. I have a
chart like
this:

Type Company 1 Company 2 Company 3
Product
Plastic 3.69 0.79 0.89
Metal 0.99 2.57 1.99
Wood 1.69 0.89 0.99

If I type in a cell A1 Plastic, then then cell A2 should tell me
the
highest
price found in the row for plastic.

I also want to be able to type plastic in A1 and then in B1 wood
and in
cell
A2 should calculate the total highest price for both.

Thanks in advance for any help.

Find amount in row.
 

Thanks. But now I can't use the Large function. I want to be able to use the
same thing to get one less the MAX or two less than Max etc. Is this possible
with your current formula?

Thanks, your'e the best T. Valko.

"T. Valko" wrote:

Assume this data is in the range F2:I4 -

Plastic, 3.69, 0.79, 0.89
Metal, 0.99, 2.57, 1.99
Wood, 1.69, 0.89, 0.99

A1 = plastic
B1 = wood

=SUMPRODUCT(SUBTOTAL(4,OFFSET(G2:I4,ROW(G2:I4)-ROW(G2),0,1)),--(ISNUMBER(MATCH(F2:F4,A1:B1,0))))

Also, what would I need to change to
to give one less then max?

You're really pressing your luck!


--
Biff
Microsoft Excel MVP


"art" wrote in message
...
Thanks. But I really want to be able to use a formula that will calculate
the
total without having to enter "+" and the entire formula for each item I
add.
I will have many companies and items, and if I want a total of the whole
thing, I'll have to enter the whole formula many times adding everything
together.

Is there no way that I can total the whole thing using an array formula or
something else, and avoide having to enter the entire fomrula for each
item?

Thanks for any help.

"Teethless mama" wrote:

Try this:
=MAX((F2:F4=A1)*G2:I4)+MAX((F2:F4=B1)*G2:I4)

ctrl+shift+enter, not just enter



"art" wrote:

Many Thanks. However, can I use a formula that will calculate both
items in
one cell and give me the total. So if I enter in A1 Plastic and in A2
Wood,
it should give me the total?

Also, what would I need to change to to give one less then max?

Thanks.

"T. Valko" wrote:

Assume this data is in the range F2:I4 -

Plastic, 3.69, 0.79, 0.89
Metal, 0.99, 2.57, 1.99
Wood, 1.69, 0.89, 0.99

A1 = plastic
B1 = wood

Enter this formula in A2 and copy across to B2:

=MAX(INDEX($G2:$I4,MATCH(A1,$F2:$F4,0),0))


--
Biff
Microsoft Excel MVP


"art" wrote in message
...
Hello All:

I would like to know how can I find out the folowing. I have a
chart like
this:

Type Company 1 Company 2 Company 3
Product
Plastic 3.69 0.79 0.89
Metal 0.99 2.57 1.99
Wood 1.69 0.89 0.99

If I type in a cell A1 Plastic, then then cell A2 should tell me
the
highest
price found in the row for plastic.

I also want to be able to type plastic in A1 and then in B1 wood
and in
cell
A2 should calculate the total highest price for both.

Thanks in advance for any help.

Find amount in row.
 

Hi,

you can try this. Suppose the table below is is in range F1:I4. In cell
J2, enter =max(G2:I2) and copy down. in cell J1, type Max

Now in cell A1 and B1, enter Type and Max respectively. In A2 and A3, enter
Plastic and Wood respectively. In cell A4, enter
=DSUM($F$1:$J$4,B1,A1:A3).

Please remember that for the Database functions I.e. DSUM, DCOUNT etc,
blanks are treated as all inclusive and therefore if you delete the entry in
cell A3, the result will be summation of all entries in the Max column.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"art" wrote in message
...
Hello All:

I would like to know how can I find out the folowing. I have a chart like
this:

Type Company 1 Company 2 Company 3
Product
Plastic 3.69 0.79 0.89
Metal 0.99 2.57 1.99
Wood 1.69 0.89 0.99

If I type in a cell A1 Plastic, then then cell A2 should tell me the
highest
price found in the row for plastic.

I also want to be able to type plastic in A1 and then in B1 wood and in
cell
A2 should calculate the total highest price for both.

Thanks in advance for any help.

Find amount in row.
 

But I also need to use this for the second to the largest and the third to
largest and so on. So I can't really use MAX. Is there a way that I can can
calculate the whole thing in one cell?


Thanks for your help.


"Ashish Mathur" wrote:

Hi,

you can try this. Suppose the table below is is in range F1:I4. In cell
J2, enter =max(G2:I2) and copy down. in cell J1, type Max

Now in cell A1 and B1, enter Type and Max respectively. In A2 and A3, enter
Plastic and Wood respectively. In cell A4, enter
=DSUM($F$1:$J$4,B1,A1:A3).

Please remember that for the Database functions I.e. DSUM, DCOUNT etc,
blanks are treated as all inclusive and therefore if you delete the entry in
cell A3, the result will be summation of all entries in the Max column.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"art" wrote in message
...
Hello All:

I would like to know how can I find out the folowing. I have a chart like
this:

Type Company 1 Company 2 Company 3
Product
Plastic 3.69 0.79 0.89
Metal 0.99 2.57 1.99
Wood 1.69 0.89 0.99

If I type in a cell A1 Plastic, then then cell A2 should tell me the
highest
price found in the row for plastic.

I also want to be able to type plastic in A1 and then in B1 wood and in
cell
A2 should calculate the total highest price for both.

Thanks in advance for any help.

Find amount in row.
 

Is this possible with your current formula?

No.

I don't know how to do what you want in a single formula.

--
Biff
Microsoft Excel MVP


"art" wrote in message
...
Thanks. But now I can't use the Large function. I want to be able to use
the
same thing to get one less the MAX or two less than Max etc. Is this
possible
with your current formula?

Thanks, your'e the best T. Valko.

"T. Valko" wrote:

Assume this data is in the range F2:I4 -

Plastic, 3.69, 0.79, 0.89
Metal, 0.99, 2.57, 1.99
Wood, 1.69, 0.89, 0.99

A1 = plastic
B1 = wood

=SUMPRODUCT(SUBTOTAL(4,OFFSET(G2:I4,ROW(G2:I4)-ROW(G2),0,1)),--(ISNUMBER(MATCH(F2:F4,A1:B1,0))))

Also, what would I need to change to
to give one less then max?

You're really pressing your luck!


--
Biff
Microsoft Excel MVP


"art" wrote in message
...
Thanks. But I really want to be able to use a formula that will
calculate
the
total without having to enter "+" and the entire formula for each item
I
add.
I will have many companies and items, and if I want a total of the
whole
thing, I'll have to enter the whole formula many times adding
everything
together.

Is there no way that I can total the whole thing using an array formula
or
something else, and avoide having to enter the entire fomrula for each
item?

Thanks for any help.

"Teethless mama" wrote:

Try this:
=MAX((F2:F4=A1)*G2:I4)+MAX((F2:F4=B1)*G2:I4)

ctrl+shift+enter, not just enter



"art" wrote:

Many Thanks. However, can I use a formula that will calculate both
items in
one cell and give me the total. So if I enter in A1 Plastic and in
A2
Wood,
it should give me the total?

Also, what would I need to change to to give one less then max?

Thanks.

"T. Valko" wrote:

Assume this data is in the range F2:I4 -

Plastic, 3.69, 0.79, 0.89
Metal, 0.99, 2.57, 1.99
Wood, 1.69, 0.89, 0.99

A1 = plastic
B1 = wood

Enter this formula in A2 and copy across to B2:

=MAX(INDEX($G2:$I4,MATCH(A1,$F2:$F4,0),0))


--
Biff
Microsoft Excel MVP


"art" wrote in message
...
Hello All:

I would like to know how can I find out the folowing. I have a
chart like
this:

Type Company 1 Company 2 Company 3
Product
Plastic 3.69 0.79 0.89
Metal 0.99 2.57 1.99
Wood 1.69 0.89 0.99

If I type in a cell A1 Plastic, then then cell A2 should tell me
the
highest
price found in the row for plastic.

I also want to be able to type plastic in A1 and then in B1 wood
and in
cell
A2 should calculate the total highest price for both.

Thanks in advance for any help.