| View previous topic :: View next topic |
| 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.
|
Thank you for your help. |
|
| Back to top |
|
 |
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) |
|
| Back to top |
|
|
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:
I tried to make it:
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. |
|
| Back to top |
|
|
JohnV
Administrator
Joined: 07 Mar 2003
Posts: 8976
Location: Lexinton, Kentucky, USA
|
| Posted: Sat Jun 23, 2012 9:24 am Post subject: |
|
|
| =A1*B1 & " cm" |
|
| Back to top |
|
|
karolus
OOo Advocate
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 |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
karolus
OOo Advocate
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 |
|
| Back to top |
|
|
zagg
General User
Joined: 08 Jun 2012
Posts: 12
|
| Posted: Sun Jun 24, 2012 2:47 am Post subject: |
|
|
Hi Karo
When I input:
into Format Code, something weird happens. After I add (hit the check button), the Format Code changes to:
In the Format box, it displays:
and in the small box to the right of the Format box and under Language, it displays:
I could, however, get column A to work but with the Text format code:
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. |
|
| Back to top |
|
|
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? |
|
| Back to top |
|
|
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:
into Format Code, something weird happens. After I add (hit the check button), the Format Code changes to:
In the Format box, it displays:
and in the small box to the right of the Format box and under Language, it displays:
I could, however, get column A to work but with the Text format code:
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 |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
karolus
OOo Advocate
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:
Karo |
|
| Back to top |
|
|
|
