[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
zagg
General User

Joined: 08 Jun 2012
Posts: 12

Posted: Sat Jun 23, 2012 6:58 am    Post subject: mulptilying 2 columns

Hi. Let's say I have 2 columns of data like so:

 Code: A       B       C 42.34 cm   1.00 90.53 cm   0.02 49.32 cm   0.53 58.39 cm   0.25  6.29 cm   0.76 10.23 cm   0.75 13.25 cm   0.44 16.33 cm   0.34    etc     etc

how would I make column C display the product of column A and column B? In other words, C1 = A1*B1, C2 = A2*B2, C3 = A3*B3, and so on.

I try using the function wizard and at the bottom under formula, I can put:
=A1*B1
for the C1 cell and that produces the desired effect for one cell and theoretically, I could do that for all the cells in column C. But of course, that is very time-consuming considering the vast amounts of data I have.
When I highlight the whole C column, I don't know how to tell it to do:
=An*Bn

I would also like to generalize to include multiple columns with complicated formulas like so:
 Code: For example, (En) = (An)*(Bn) + (An)^2 +((Cn) - 6)^3 - ( 1/(Dn) + (3*(Cn)^2)/((An)+4) )(Bn) where n is 1, 2, 3, etc.

Robert Tucker
Moderator

Joined: 16 Aug 2004
Posts: 3367
Location: Manchester UK

Posted: Sat Jun 23, 2012 7:39 am    Post subject: Re: mulptilying 2 columns

 zagg wrote: I can put: =A1*B1 for the C1 cell and that produces the desired effect for one cell and theoretically, I could do that for all the cells in column C. But of course, that is very time-consuming considering the vast amounts of data I have. When I highlight the whole C column, I don't know how to tell it to do: =An*Bn

You can enter C1:C8 in the box to the left of the formula box and hit Enter, then Edit>Fill>Down (Ctrl+D) or you can use the mouse to pull down the little black box at the bottom right corner of C1.
_________________
LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot)
zagg
General User

Joined: 08 Jun 2012
Posts: 12

Posted: Sat Jun 23, 2012 9:08 am    Post subject:

ah! It worked perfectly! Thank you!

One more thing maybe you can help me with...

now, the C column displays the product of column A and Column B. But is there any way I can have it display the number followed by a unit. So in the example:
Cell C1 shows 42.34
(A1*B1 = 42.34 * 1.00 = 42.34)
Cell C2 shows 1.8106
(A2*B2 = 90.53 * 0.02 = 1.8106)
and etc.
Is there any way to get
Cell C1 to show 42.34 cm
Cell C2 to show 1.8106 cm
and etc.

In the formula bar, where it shows:
 Code: =A1*B1

I tried to make it:
 Code: =A1*B1 cm

but that gives me an Err:509 in the Cell. I assume that's because I'm trying to mix text in with the formula. Is there some way to do this?

Thanks.
JohnV

Joined: 07 Mar 2003
Posts: 8976
Location: Lexinton, Kentucky, USA

 Posted: Sat Jun 23, 2012 9:24 am    Post subject: =A1*B1 & " cm"
karolus

Joined: 22 Jun 2011
Posts: 208

Posted: Sat Jun 23, 2012 9:46 am    Post subject:

 JohnV wrote: =A1*B1 & " cm"

No, no, no!

give Column C same Format as Column A instead.

Karo
zagg
General User

Joined: 08 Jun 2012
Posts: 12

Posted: Sat Jun 23, 2012 6:47 pm    Post subject:

thanks guys for replies...

JohnV: that works but now I have another slight problem.

after using =A1*B1 & " cm" then using Fill, Down, the numbers come out with the unit as I wanted but now, they have varying decimal places and I can not adjust them.

In other words they come out as:
 Code: 1.11 cm  2.222 cm    3.3 cm 4.4444 cm  5.555 cm   6.66 cm

Is there anyway to get them like:
 Code: 1.1100 cm  2.2220 cm  3.3000 cm  4.4444 cm  5.5550 cm  6.6600 cm

no amount of adding or deleting decimal places will change it. If the unit is not included (without the " cm") then adding/deleting decimal places has the desired effect.

EDIT: I just noticed that my A column also has this problem. I have it formatted as text with "@ cm" as format code and it will display (for example) 12.2 cm instead of 12.20 cm and no amount of adding decimal places will change that either... so hopefully, any potential solution can fix this as well.

karolus: I had tried that as well... I went to Format Cells, then under Category: Text, I have "@ cm" under Format code and that just results in all the cells in column C to show "cm" with no numbers.
karolus

Joined: 22 Jun 2011
Posts: 208

 Posted: Sat Jun 23, 2012 9:53 pm    Post subject: Hallo Format Column A and Column C with Numberformatcode: Standard" cm" Karo
zagg
General User

Joined: 08 Jun 2012
Posts: 12

Posted: Sun Jun 24, 2012 2:47 am    Post subject:

Hi Karo

When I input:

 Code: Standard" cm"
into Format Code, something weird happens. After I add (hit the check button), the Format Code changes to:

 Code: StanDaRD" cm"

In the Format box, it displays:

 Code: 46tan22a576022 GB

and in the small box to the right of the Format box and under Language, it displays:

 Code: 46tan21a566321 GB

I could, however, get column A to work but with the Text format code:

 Code: @" cm"

This however did not (and nor did the Standard" cm") work for Column C. It would again only show cm when I changed the format.

I think what's the issue is that all the data in Column A, I input manually, whereas Column C is represented as a formula (A1*B1). Indeed, if I just input data manually into column C, I can get the desired effect and still be able to adjust the amount of decimal places.
zagg
General User

Joined: 08 Jun 2012
Posts: 12

 Posted: Sun Jun 24, 2012 3:49 am    Post subject: just to recap: it seems that as long as formula is =A1*B1, then the cell can be formatted freely. Thus I can have it show the desired amount of decimal places. However, if the formula is =A1*B1 & " cm", then the cell cannot be formatted and the number shown defaults to "General" (which is Decimal Places: 0 and Leading Zeroes: 1). This produces the undesired effect of different amount of decimal places for different numbers. If there is no solution to this problem, then it is not really a big deal. There are ways around it like having the formula as A1*B1 & "0 cm" for those needing 1 extra zero and A1*B1 & "00 cm" for those needing 2 extra zeroes, and so forth, though this could be time-consuming for large amounts of data. Also, creating an extra column after the data (C) column aligned left with just "cm" so that visually, it is nearly the same... It would be nice to have a solution though... maybe some kind of script?
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

Posted: Sun Jun 24, 2012 5:28 am    Post subject:

zagg wrote:
Hi Karo

When I input:

 Code: Standard" cm"
into Format Code, something weird happens. After I add (hit the check button), the Format Code changes to:

 Code: StanDaRD" cm"

In the Format box, it displays:

 Code: 46tan22a576022 GB

and in the small box to the right of the Format box and under Language, it displays:

 Code: 46tan21a566321 GB

I could, however, get column A to work but with the Text format code:

 Code: @" cm"

This however did not (and nor did the Standard" cm") work for Column C. It would again only show cm when I changed the format.

I think what's the issue is that all the data in Column A, I input manually, whereas Column C is represented as a formula (A1*B1). Indeed, if I just input data manually into column C, I can get the desired effect and still be able to adjust the amount of decimal places.

Look at the format code of an "unformatted" cell. In English language the unspecific all purpose number format is named "General".
The trick is: Use any appropriate number format and append the literal string " cm" (including the double quotes).
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org
zagg
General User

Joined: 08 Jun 2012
Posts: 12

 Posted: Sun Jun 24, 2012 7:32 am    Post subject: Villeroy: yes I know, that's what @ " cm" does... the problem with "General" number format is that it only displays the significant digits without trailing zeros which is what I need. I need it to show 2.3000 cm not 2.3 cm. As my last post said, once the string " cm" is appended, the number format defaults to "General" and can not be switched to (for example) 1234.5678 Only after removing the " cm" string, does it allow switiching number formats again.
karolus

Joined: 22 Jun 2011
Posts: 208

Posted: Sun Jun 24, 2012 7:45 am    Post subject:

Hallo

@Villeroy:
Thanks for Translation 'Standard' → 'General'

@Zagg:
'@' stands for 'Text' - please forget it in Numberformat, try Formatcode:

 Code: 0.0000" cm"

Karo
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum