[edwinhwhw87]

Dear all,

In the course of my work I do lots of calculations similar to the attachment enclosed below.

1. Dimensions have to be converted to cubic metres
2. Weight has to be converted to metric tons
3. RT column chooses between volume or weight value, whichever is greater

Typical RT formula : =IF(B2*C2*D2/1000000E2/1000,B2*C2*D2/1000000,E2/1000)

It's really simpler and faster to compute using a calculator, however, with excel formulas, errors in calculation are massively minimised.

Herein lies the problem - my spreadsheets are hundreds of items long. If I have to manually insert formula for every RT cell, it will take hours and that of course will be counter productive.

Is there any way in which Excel 2013 can autofill or be formatted to automatically insert the formula with some minor changes?

Example of how my formula differs in RT column, from cell to cell:-

F2 =IF(B2*C2*D2/1000000E2/1000,B2*C2*D2/1000000,E2/1000)
F3 =IF(B3*C3*D3/1000000E3/1000,B3*C3*D3/1000000,E3/1000)
F4 =IF(B4*C4*D4/1000000E4/1000,B4*C4*D4/1000000,E4/1000)

and the list goes on.

Many many thanks in advance.

[Walter Briscoe]

In message of Wed, 6 Aug 2014
07:42:58 in microsoft.public.excel.worksheet.functions, edwinhwhw87
writes

Dear all,

In the course of my work I do lots of calculations similar to the
attachment enclosed below.

1. Dimensions have to be converted to cubic metres
2. Weight has to be converted to metric tons
3. RT column chooses between volume or weight value, whichever is
greater

Typical RT formula :
=IF(B2*C2*D2/1000000E2/1000,B2*C2*D2/1000000,E2/1000)

It's really simpler and faster to compute using a calculator, however,
with excel formulas, errors in calculation are massively minimised.

Herein lies the problem - my spreadsheets are hundreds of items long. If
I have to manually insert formula for every RT cell, it will take hours
and that of course will be counter productive.

Is there any way in which Excel 2013 can autofill or be formatted to
automatically insert the formula with some minor changes?

Example of how my formula differs in RT column, from cell to cell:-

F2 =IF(B2*C2*D2/1000000E2/1000,B2*C2*D2/1000000,E2/1000)
F3 =IF(B3*C3*D3/1000000E3/1000,B3*C3*D3/1000000,E3/1000)
F4 =IF(B4*C4*D4/1000000E4/1000,B4*C4*D4/1000000,E4/1000)

and the list goes on.

Many many thanks in advance.


+-------------------------------------------------------------------+
|Filename: Example 1.jpg |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=981|
+-------------------------------------------------------------------+



In F2, put =IF(B2*C2*D2/1000000E2/1000,B2*C2*D2/1000000,E2/1000)
Select F2 and copy it with Ctrl+C
Select F3 to the bottom of the page and "copy down" with Ctrl+V.
If F3 to the bottom have the same value as F2, click function key F9
to refresh those values.
Otherwise, save the file and reopen it to find the values you want.
I see this behaviour in Excel 2003 and expect to see it in 2013.
--
Walter Briscoe

[edwinhwhw87]

Originally Posted by Walter Briscoe

In message of Wed, 6 Aug 2014
07:42:58 in microsoft.public.excel.worksheet.functions, edwinhwhw87
writes

Dear all,

In the course of my work I do lots of calculations similar to the
attachment enclosed below.

1. Dimensions have to be converted to cubic metres
2. Weight has to be converted to metric tons
3. RT column chooses between volume or weight value, whichever is
greater

Typical RT formula :
=IF(B2*C2*D2/1000000E2/1000,B2*C2*D2/1000000,E2/1000)

It's really simpler and faster to compute using a calculator, however,
with excel formulas, errors in calculation are massively minimised.

Herein lies the problem - my spreadsheets are hundreds of items long. If
I have to manually insert formula for every RT cell, it will take hours
and that of course will be counter productive.

Is there any way in which Excel 2013 can autofill or be formatted to
automatically insert the formula with some minor changes?

Example of how my formula differs in RT column, from cell to cell:-

F2 =IF(B2*C2*D2/1000000E2/1000,B2*C2*D2/1000000,E2/1000)
F3 =IF(B3*C3*D3/1000000E3/1000,B3*C3*D3/1000000,E3/1000)
F4 =IF(B4*C4*D4/1000000E4/1000,B4*C4*D4/1000000,E4/1000)

and the list goes on.

Many many thanks in advance.


+-------------------------------------------------------------------+
|Filename: Example 1.jpg |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=981|
+-------------------------------------------------------------------+



In F2, put =IF(B2*C2*D2/1000000E2/1000,B2*C2*D2/1000000,E2/1000)
Select F2 and copy it with Ctrl+C
Select F3 to the bottom of the page and "copy down" with Ctrl+V.
If F3 to the bottom have the same value as F2, click function key F9
to refresh those values.
Otherwise, save the file and reopen it to find the values you want.
I see this behaviour in Excel 2003 and expect to see it in 2013.
--
Walter Briscoe

Dear Walter Briscoe

Thanks for your reply.

However, values for F column are dynamic and would depend on some basic calculation between other cells before determining which is the greater value of 2 units.

I do not really understand how your method works in expediting the formulation of my spreadsheet.

Copying down the formula from F3 downwards will only result in the entire column having the same value because the parameters used are only B2 / C2 / D2 / E2.

What I need is for the formula to change accordingly to use B3 - E3 for F3, B4 - E4 for F4, B5 - E5 for F5, and so on and so forth.


I've also received a reply from another forum saying that I should use tables with the following formula:

=MID(SUBSTITUTE(A2,"X",REPT(" ",LEN(A2))),1+LEN(A2)*0,LEN(A2)*1)

????

Walter Briscoe or anyone else - care to explain?

[edwinhwhw87]

Wish to add,

Assuming that I have all the values (dimension and weight) in spreadsheet, whoever is able to come up with a set procedure to achieve my desired calculations (for infinite rows) within 10 mins will get a USD10 tip through paypal ~

[Claus Busch]

Hi Edwin,

Am Wed, 6 Aug 2014 07:42:58 +0100 schrieb edwinhwhw87:

Typical RT formula :
=IF(B2*C2*D2/1000000E2/1000,B2*C2*D2/1000000,E2/1000)

in F2 try:
=MAX(B2%*C2%*D2%;E2/1000)

then go to the right lower edge of the cell F2 till the cursor changes
to a black + sign and drag the formula with pressed left mouse button
down


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[Walter Briscoe]

In message of Fri, 8 Aug 2014
09:02:42 in microsoft.public.excel.worksheet.functions, edwinhwhw87
writes

Walter Briscoe;1618357 Wrote:
In message of Wed, 6 Aug 2014
07:42:58 in microsoft.public.excel.worksheet.functions, edwinhwhw87
writes-

Dear all,

In the course of my work I do lots of calculations similar to the
attachment enclosed below.

1. Dimensions have to be converted to cubic metres
2. Weight has to be converted to metric tons
3. RT column chooses between volume or weight value, whichever is
greater

Typical RT formula :
=IF(B2*C2*D2/1000000E2/1000,B2*C2*D2/1000000,E2/1000)

It's really simpler and faster to compute using a calculator, however,
with excel formulas, errors in calculation are massively minimised.

Herein lies the problem - my spreadsheets are hundreds of items long.
If
I have to manually insert formula for every RT cell, it will take
hours
and that of course will be counter productive.

Is there any way in which Excel 2013 can autofill or be formatted to
automatically insert the formula with some minor changes?

Example of how my formula differs in RT column, from cell to cell:-

F2 =IF(B2*C2*D2/1000000E2/1000,B2*C2*D2/1000000,E2/1000)
F3 =IF(B3*C3*D3/1000000E3/1000,B3*C3*D3/1000000,E3/1000)
F4 =IF(B4*C4*D4/1000000E4/1000,B4*C4*D4/1000000,E4/1000)

and the list goes on.

Many many thanks in advance.


+-------------------------------------------------------------------+
|Filename: Example 1.jpg |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=981|
+-------------------------------------------------------------------+


-
In F2, put =IF(B2*C2*D2/1000000E2/1000,B2*C2*D2/1000000,E2/1000)
Select F2 and copy it with Ctrl+C
Select F3 to the bottom of the page and "copy down" with Ctrl+V.
If F3 to the bottom have the same value as F2, click function key F9
to refresh those values.
Otherwise, save the file and reopen it to find the values you want.
I see this behaviour in Excel 2003 and expect to see it in 2013.
--
Walter Briscoe

Dear Walter Briscoe

Thanks for your reply.

However, values for F column are dynamic and would depend on some basic
calculation between other cells before determining which is the greater
value of 2 units.

I do not really understand how your method works in expediting the
formulation of my spreadsheet.

I do not really understand that you tried what I suggested.
Please do so and report your experience.


Copying down the formula from F3 downwards will only result in the
entire column having the same value because the parameters used are only
B2 / C2 / D2 / E2.

What I need is for the formula to change accordingly to use B3 - E3 for
F3, B4 - E4 for F4, B5 - E5 for F5, and so on and so forth.


I've also received a reply from another forum saying that I should use
tables with the following formula:

=MID(SUBSTITUTE(A2,"X",REPT(" ",LEN(A2))),1+LEN(A2)*0,LEN(A2)*1)

????

Walter Briscoe or anyone else - care to explain?


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+




--
Walter Briscoe