Results 1 to 17 of 17
  1. #1
    mtbr member
    Reputation: TwoNin9r's Avatar
    Join Date
    Jan 2011
    Posts
    1,816

    Basic MS Excel help, bike related!

    hey guys, i'm trying to make an Excel spreadsheet but i'm kind of excel retarded. what i want is this...

    i want to have a parts list and then a new part that it lighter, my columns are as follows

    Part weight gr weight lbs

    X A
    B Y

    what i'm trying to accomplish is that when i enter a given number of grams where "X" is, the number is converted to lbs where "A" is and the converse for "Y" and "B"

    does anyone know what forumala i use or how this can be done?

  2. #2
    Is it Friday yet?
    Reputation: kwrides's Avatar
    Join Date
    Oct 2010
    Posts
    598
    In column A, type =X/453.59237

    For X, don't type the letter, just click on the cell, then complete the formula in column A

    If you want to repeat this, click on A1, then the bottom right corner, and drag down, it should fill them with X1, X2, X3, etc

    Just do the opposite conversion for B Y
    Last edited by kwrides; 08-02-2011 at 07:48 AM. Reason: X, not A

  3. #3
    mtbr member
    Reputation: TwoNin9r's Avatar
    Join Date
    Jan 2011
    Posts
    1,816
    Yesss!!!!

  4. #4
    mtbr member
    Reputation: TwoNin9r's Avatar
    Join Date
    Jan 2011
    Posts
    1,816
    ok wait... this works for grams... but now, when i put a number in the Lbs section it doesn't come up as grams... so i try the inverse formula in the other column and it tells me it's a circular reference (which makes sense) and it can't be done

  5. #5
    mtbr member
    Reputation: canuckbiker's Avatar
    Join Date
    May 2010
    Posts
    63
    What's the actual sheet layout you want?

    I did this as a test:

    GRAMS TO POUNDS

    Cell A2...............Cell B2
    100.....................=SUM(A2/453.59237)

    POUNDS TO GRAMS

    Cell A3...........Cell B3
    100................=SUM(A3*453.59237)

    Is that what you want? If not, let me know and I can probably whip it up for you.

    ~Andrew

  6. #6
    Is it Friday yet?
    Reputation: kwrides's Avatar
    Join Date
    Oct 2010
    Posts
    598
    Assuming you want to enter pounds in B and get grams in Y,

    Y would be =B*453.59237

    Again, don't type 'B', click on the cell in your formula.
    Last edited by kwrides; 08-02-2011 at 02:00 PM. Reason: I'm a dumbass

  7. #7
    mtbr member
    Reputation: TwoNin9r's Avatar
    Join Date
    Jan 2011
    Posts
    1,816
    i got the formula right, so now when i put a number in the gams column, it spits out lbs. but i wanted it to be so that, let's say i only have a weight in lbs, i can just type the weight in the lbs column and the number will pop up in the grams column converted.

    in essence, the two columns interact so that no matter which column i put a number in, it does the conversion to the other unit in it's respective column

    so if i have

    | Grams | LBs | ...i can enter a number
    X ?
    Y ?
    ? Z
    A ?
    ? B

    and the "?" will all pop up with the corrected #

  8. #8
    Is it Friday yet?
    Reputation: kwrides's Avatar
    Join Date
    Oct 2010
    Posts
    598
    You're talking about circular references, which won't work. So, just set up 2 rows. Row 1 converts grams to pounds, row 2 converts pounds to grams.

  9. #9
    Made in Canada
    Reputation: tpm7's Avatar
    Join Date
    May 2007
    Posts
    415
    Now you're getting fancy, from the sounds of what you want to do you're going to need a Macro or you might be able to get away with using logic functions, and both are more advanced. As far as I know, there is no simple way to do what you are describing there... maybe someone knows? Google is usually a good help, you just need to get the search terms right.
    STOLEN: '07 Banshee Viento - See Eastern Canada Forum for Pictures. If anyone sees it contact me ASAP!

  10. #10
    mtbr member
    Reputation: TwoNin9r's Avatar
    Join Date
    Jan 2011
    Posts
    1,816
    @KW yeah that's the error i was getting, because the computer needs a value in order to use the formula. i guess a circular function would create a 4th dimension or something hehe

    @tpm yeah i searched... it's really NBD i'll just convert to grams if i need to... 99% of this stuff in is g's anyway

  11. #11
    Is it Friday yet?
    Reputation: kwrides's Avatar
    Join Date
    Oct 2010
    Posts
    598
    Quote Originally Posted by 4x4runner View Post
    @KW yeah that's the error i was getting, because the computer needs a value in order to use the formula. i guess a circular function would create a 4th dimension or something hehe

    @tpm yeah i searched... it's really NBD i'll just convert to grams if i need to... 99% of this stuff in is g's anyway
    Yep, I'm a used ta be programmer and one or our biggest nightmares are circular references. The query just loops forever and kills everything.

  12. #12
    mtbr member
    Reputation:
    Join Date
    May 2006
    Posts
    585
    If all gram weights are entered 454G (with a G following the number) and pounds as a raw number 1.35

    =IF(ISNUMBER(FIND("G",A1)),MID(A1,1,FIND("G",A1)-1)/454,A1)

    The formula looks for and remembers the position of G in the weight string. It then strips the string of the G, divides by the 454 and gives the answer. If no G is present, it gives the answer.

    This formula will give your weights in pounds.

    I just used 454 not 453.xxxxx

    update as you wish
    http://facebook.com/CharlemontTrails
    NEMBA Past President...

  13. #13
    mtbr member
    Reputation: TwoNin9r's Avatar
    Join Date
    Jan 2011
    Posts
    1,816
    Quote Originally Posted by hado_pv View Post
    If all gram weights are entered 454G (with a G following the number) and pounds as a raw number 1.35

    =IF(ISNUMBER(FIND("G",A1)),MID(A1,1,FIND("G",A1)-1)/454,A1)

    The formula looks for and remembers the position of G in the weight string. It then strips the string of the G, divides by the 454 and gives the answer. If no G is present, it gives the answer.

    This formula will give your weights in pounds.

    I just used 454 not 453.xxxxx

    update as you wish
    ok... but how do magnets work? lol holy crap ok i'll try that

  14. #14
    mtbr member
    Reputation:
    Join Date
    Jun 2007
    Posts
    1,824
    Nice solution hado. Using the original A B X Y layout, you could use if, not, and isblank functions to determine which cell holds the data. A1 would be: =IF(NOT(ISBLANK(X1)),X1/354,Y1)

  15. #15
    mtbr member
    Reputation: TwoNin9r's Avatar
    Join Date
    Jan 2011
    Posts
    1,816
    lol this thread changed languages quick. someone PM me their email address and i'll send you the file i've created if you really want to mess with it.

  16. #16
    Hi.
    Reputation: jtmartino's Avatar
    Join Date
    Jul 2008
    Posts
    3,848
    4x4, I made you a pretty sweet spreadsheet. PM me with your email and I can shoot it your direction.

  17. #17
    Is it Friday yet?
    Reputation: kwrides's Avatar
    Join Date
    Oct 2010
    Posts
    598
    You guys are awesome. I was just trying to give him a quick and simple solution and you went and created him something cool.

    Positive rep for all!

Similar Threads

  1. Anyone have an excel sheet for building a bike?
    By FireLikeIYA in forum Weight Weenies
    Replies: 5
    Last Post: 07-16-2010, 09:32 PM
  2. New Guy, Basic Bike Build
    By ColoradoKevin in forum Beginner's Corner
    Replies: 13
    Last Post: 05-26-2010, 07:24 AM
  3. New bike - basic necessities?
    By Waves77 in forum Beginner's Corner
    Replies: 12
    Last Post: 10-14-2008, 11:18 AM
  4. Replies: 17
    Last Post: 03-24-2008, 07:38 PM
  5. Basic workout for basic rider.
    By johnny_99 in forum XC Racing and Training
    Replies: 2
    Last Post: 12-16-2004, 05:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •