OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

mulptilying 2 columns

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
zagg
General User
General User


Joined: 08 Jun 2012
Posts: 12

PostPosted: Sat Jun 23, 2012 6:58 am    Post subject: mulptilying 2 columns Reply with quote

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
View user's profile Send private message
Robert Tucker
Moderator
Moderator


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

PostPosted: Sat Jun 23, 2012 7:39 am    Post subject: Re: mulptilying 2 columns Reply with quote

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.
_________________
OpenOffice 4.0.0 and LibreOffice 4.x.x on Fedora 20, Ubuntu 13.10, Windows 8.1 Preview (Triple Boot)
Back to top
View user's profile Send private message
zagg
General User
General User


Joined: 08 Jun 2012
Posts: 12

PostPosted: Sat Jun 23, 2012 9:08 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
JohnV
Administrator
Administrator


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

PostPosted: Sat Jun 23, 2012 9:24 am    Post subject: Reply with quote

=A1*B1 & " cm"
Back to top
View user's profile Send private message
karolus
OOo Advocate
OOo Advocate


Joined: 22 Jun 2011
Posts: 210

PostPosted: Sat Jun 23, 2012 9:46 am    Post subject: Reply with quote

JohnV wrote:
=A1*B1 & " cm"


No, no, no!

give Column C same Format as Column A instead.

Karo
Back to top
View user's profile Send private message
zagg
General User
General User


Joined: 08 Jun 2012
Posts: 12

PostPosted: Sat Jun 23, 2012 6:47 pm    Post subject: Reply with quote

thanks guys for replies...

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

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
View user's profile Send private message
karolus
OOo Advocate
OOo Advocate


Joined: 22 Jun 2011
Posts: 210

PostPosted: Sat Jun 23, 2012 9:53 pm    Post subject: Reply with quote

Hallo

Format Column A and Column C with Numberformatcode:

Standard" cm"

Karo
Back to top
View user's profile Send private message
zagg
General User
General User


Joined: 08 Jun 2012
Posts: 12

PostPosted: Sun Jun 24, 2012 2:47 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
zagg
General User
General User


Joined: 08 Jun 2012
Posts: 12

PostPosted: Sun Jun 24, 2012 3:49 am    Post subject: Reply with quote

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? Smile
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Jun 24, 2012 5:28 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
zagg
General User
General User


Joined: 08 Jun 2012
Posts: 12

PostPosted: Sun Jun 24, 2012 7:32 am    Post subject: Reply with quote

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
View user's profile Send private message
karolus
OOo Advocate
OOo Advocate


Joined: 22 Jun 2011
Posts: 210

PostPosted: Sun Jun 24, 2012 7:45 am    Post subject: Reply with quote

Hallo

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


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

Code:
0.0000" cm"


Karo
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group