Page 2 (summary)
RAB No CERTIFIED QUANTITY OF STEEL IN MT
8mm 10mm 12mm 16mm 20mm 25mm

Sum 13.698 - - 49.710 109.236 -


Page 1
Received Dia Qty Rate difference

16 25 15000 25.00(forumla required)
16 42.5 12000 24.71it should shows(49.710-25) sa same dia
8 11 13000 11(it should show)
20 25.00 10250
8 8.50 11500 2.698(13.698-11( which is same dia as above)
20 16.37 13500
8 5.00 14800


summary may not necessary to be in Page-2 it may in page1 also

if not understood i will send my excel sheet please send me the e-mail id
my email id is

where your summary data is on Page2 B1:G2 and your received data is on Page 1
A2:C8, try this in cell D2 and copy down.

=MEDIAN(B2,0,HLOOKUP(A2,'Page 2'!$B$1:$G$2,2,0)-SUMIF(A$2:A2,A2,B$2:B2)+B2)

"vmohan1978" wrote:

Page 2 (summary)
RAB No CERTIFIED QUANTITY OF STEEL IN MT
8mm 10mm 12mm 16mm 20mm 25mm

Sum 13.698 - - 49.710 109.236 -


Page 1
Received Dia Qty Rate difference

16 25 15000 25.00(forumla required)
16 42.5 12000 24.71it should shows(49.710-25) sa same dia
8 11 13000 11(it should show)
20 25.00 10250
8 8.50 11500 2.698(13.698-11( which is same dia as above)
20 16.37 13500
8 5.00 14800


summary may not necessary to be in Page-2 it may in page1 also

if not understood i will send my excel sheet please send me the e-mail id
my email id is

Hi JMB,
It worked perfectly , is it possible to show result in the following manner.

IN SHEET-1
received used rate 8mm 12mm 16mm 20mm 25mm 32mm
dia qty

16 25 15000 0 0 25 0 0
0

16 42.5 12000 0 0 24.71 0 0
0

8 11 15000 11 0 0 0 0
0

8 8.5 15000 2.698 0 0 0
0 0










"JMB" wrote:

where your summary data is on Page2 B1:G2 and your received data is on Page 1
A2:C8, try this in cell D2 and copy down.

=MEDIAN(B2,0,HLOOKUP(A2,'Page 2'!$B$1:$G$2,2,0)-SUMIF(A$2:A2,A2,B$2:B2)+B2)

"vmohan1978" wrote:

Page 2 (summary)
RAB No CERTIFIED QUANTITY OF STEEL IN MT
8mm 10mm 12mm 16mm 20mm 25mm

Sum 13.698 - - 49.710 109.236 -


Page 1
Received Dia Qty Rate difference

16 25 15000 25.00(forumla required)
16 42.5 12000 24.71it should shows(49.710-25) sa same dia
8 11 13000 11(it should show)
20 25.00 10250
8 8.50 11500 2.698(13.698-11( which is same dia as above)
20 16.37 13500
8 5.00 14800


summary may not necessary to be in Page-2 it may in page1 also

if not understood i will send my excel sheet please send me the e-mail id
my email id is

Apologize for delayed response. Had to take a last minute business trip and
computer access was limited.

Yes, I believe you can set it up this way. Assume your column headings are
in D1:I1 and your row labels are in A2:A5. Use an IF statement to see if the
row label is equal to your column header. Using the formula previously given
and making some small adjustments to fix row/column references where needed,
should look something like:

=IF(D$1=$A2,MEDIAN($B2,0,HLOOKUP($A2,'Page
2'!$B$1:$G$2,2,0)-SUMIF($A$2:$A2,$A2,$B$2:$B2)+$B2),0)

Then copy the formula across and down.

Since the row labels appear to be numeric (they must match the table on Page
2), so must your column headers for this to work. So you cannot enter the
number 16 in the row labels and 16mm in the column headers. However, If you
want "16 mm" displayed as your header instead of just 16, click Format,
Cells, select custom, enter 0" mm" in the text box and click OK. Now when
you enter 16 in that cell, XL will display "16 mm", but the cell contents
will be equivalent to your row labels and allow the formula to still work.

"vmohan1978" wrote:

Hi JMB,
It worked perfectly , is it possible to show result in the following manner.

IN SHEET-1
received used rate 8mm 12mm 16mm 20mm 25mm 32mm
dia qty

16 25 15000 0 0 25 0 0
0

16 42.5 12000 0 0 24.71 0 0
0

8 11 15000 11 0 0 0 0
0

8 8.5 15000 2.698 0 0 0
0 0










"JMB" wrote:

where your summary data is on Page2 B1:G2 and your received data is on Page 1
A2:C8, try this in cell D2 and copy down.

=MEDIAN(B2,0,HLOOKUP(A2,'Page 2'!$B$1:$G$2,2,0)-SUMIF(A$2:A2,A2,B$2:B2)+B2)

"vmohan1978" wrote:

Page 2 (summary)
RAB No CERTIFIED QUANTITY OF STEEL IN MT
8mm 10mm 12mm 16mm 20mm 25mm

Sum 13.698 - - 49.710 109.236 -


Page 1
Received Dia Qty Rate difference

16 25 15000 25.00(forumla required)
16 42.5 12000 24.71it should shows(49.710-25) sa same dia
8 11 13000 11(it should show)
20 25.00 10250
8 8.50 11500 2.698(13.698-11( which is same dia as above)
20 16.37 13500
8 5.00 14800


summary may not necessary to be in Page-2 it may in page1 also

if not understood i will send my excel sheet please send me the e-mail id
my email id is

Hi JMB
Sorry for late response:( . It worked perfectly thank you very much for your
help it saved my lot of time.
Can you give some reference of website where i can learn the excel formulas.
Once again Thank you very much:)
Take Care.

"JMB" wrote:

Apologize for delayed response. Had to take a last minute business trip and
computer access was limited.

Yes, I believe you can set it up this way. Assume your column headings are
in D1:I1 and your row labels are in A2:A5. Use an IF statement to see if the
row label is equal to your column header. Using the formula previously given
and making some small adjustments to fix row/column references where needed,
should look something like:

=IF(D$1=$A2,MEDIAN($B2,0,HLOOKUP($A2,'Page
2'!$B$1:$G$2,2,0)-SUMIF($A$2:$A2,$A2,$B$2:$B2)+$B2),0)

Then copy the formula across and down.

Since the row labels appear to be numeric (they must match the table on Page
2), so must your column headers for this to work. So you cannot enter the
number 16 in the row labels and 16mm in the column headers. However, If you
want "16 mm" displayed as your header instead of just 16, click Format,
Cells, select custom, enter 0" mm" in the text box and click OK. Now when
you enter 16 in that cell, XL will display "16 mm", but the cell contents
will be equivalent to your row labels and allow the formula to still work.

"vmohan1978" wrote:

Hi JMB,
It worked perfectly , is it possible to show result in the following manner.

IN SHEET-1
received used rate 8mm 12mm 16mm 20mm 25mm 32mm
dia qty

16 25 15000 0 0 25 0 0
0

16 42.5 12000 0 0 24.71 0 0
0

8 11 15000 11 0 0 0 0
0

8 8.5 15000 2.698 0 0 0
0 0










"JMB" wrote:

where your summary data is on Page2 B1:G2 and your received data is on Page 1
A2:C8, try this in cell D2 and copy down.

=MEDIAN(B2,0,HLOOKUP(A2,'Page 2'!$B$1:$G$2,2,0)-SUMIF(A$2:A2,A2,B$2:B2)+B2)

"vmohan1978" wrote:

Page 2 (summary)
RAB No CERTIFIED QUANTITY OF STEEL IN MT
8mm 10mm 12mm 16mm 20mm 25mm

Sum 13.698 - - 49.710 109.236 -


Page 1
Received Dia Qty Rate difference

16 25 15000 25.00(forumla required)
16 42.5 12000 24.71it should shows(49.710-25) sa same dia
8 11 13000 11(it should show)
20 25.00 10250
8 8.50 11500 2.698(13.698-11( which is same dia as above)
20 16.37 13500
8 5.00 14800


summary may not necessary to be in Page-2 it may in page1 also

if not understood i will send my excel sheet please send me the e-mail id
my email id is

if you go to mvps.org and click the excel link on the right hand side, it
will bring up a page of links to various sites. I've not been to all of
them, but I know the following have quite a bit on excel formulas:
Chip Pearson
David McRitchie - also has tutorials
Debra Dalgleish

Also, I can recommend John Walkenbach's books on excel formulae. Many of
the excel sites previously mentioned have book recommendations.

Also, a few sites that are not listed on mvps.org that I like are
xldynamic.com - definitely check the white paper on sumproduct
mcgimpsey.com

And this website has quite a lot to offer in learning excel formulae. There
are a number excel experts on this site to learn from.



"vmohan1978" wrote:

Hi JMB
Sorry for late response:( . It worked perfectly thank you very much for your
help it saved my lot of time.
Can you give some reference of website where i can learn the excel formulas.
Once again Thank you very much:)
Take Care.

"JMB" wrote:

Apologize for delayed response. Had to take a last minute business trip and
computer access was limited.

Yes, I believe you can set it up this way. Assume your column headings are
in D1:I1 and your row labels are in A2:A5. Use an IF statement to see if the
row label is equal to your column header. Using the formula previously given
and making some small adjustments to fix row/column references where needed,
should look something like:

=IF(D$1=$A2,MEDIAN($B2,0,HLOOKUP($A2,'Page
2'!$B$1:$G$2,2,0)-SUMIF($A$2:$A2,$A2,$B$2:$B2)+$B2),0)

Then copy the formula across and down.

Since the row labels appear to be numeric (they must match the table on Page
2), so must your column headers for this to work. So you cannot enter the
number 16 in the row labels and 16mm in the column headers. However, If you
want "16 mm" displayed as your header instead of just 16, click Format,
Cells, select custom, enter 0" mm" in the text box and click OK. Now when
you enter 16 in that cell, XL will display "16 mm", but the cell contents
will be equivalent to your row labels and allow the formula to still work.

"vmohan1978" wrote:

Hi JMB,
It worked perfectly , is it possible to show result in the following manner.

IN SHEET-1
received used rate 8mm 12mm 16mm 20mm 25mm 32mm
dia qty

16 25 15000 0 0 25 0 0
0

16 42.5 12000 0 0 24.71 0 0
0

8 11 15000 11 0 0 0 0
0

8 8.5 15000 2.698 0 0 0
0 0










"JMB" wrote:

where your summary data is on Page2 B1:G2 and your received data is on Page 1
A2:C8, try this in cell D2 and copy down.

=MEDIAN(B2,0,HLOOKUP(A2,'Page 2'!$B$1:$G$2,2,0)-SUMIF(A$2:A2,A2,B$2:B2)+B2)

"vmohan1978" wrote:

Page 2 (summary)
RAB No CERTIFIED QUANTITY OF STEEL IN MT
8mm 10mm 12mm 16mm 20mm 25mm

Sum 13.698 - - 49.710 109.236 -


Page 1
Received Dia Qty Rate difference

16 25 15000 25.00(forumla required)
16 42.5 12000 24.71it should shows(49.710-25) sa same dia
8 11 13000 11(it should show)
20 25.00 10250
8 8.50 11500 2.698(13.698-11( which is same dia as above)
20 16.37 13500
8 5.00 14800


summary may not necessary to be in Page-2 it may in page1 also

if not understood i will send my excel sheet please send me the e-mail id
my email id is