Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Jan 2010
    Location
    Queensland Australia
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Working with "ranges" within Excel

    Would some kind soul assist me please.
    Say I have a range of A1:E20 filled with numbers.
    Now I would like all the cells to be divided by a number in cell F1.
    How can I use an operant on all the cells within a range?
    Please see attached file as an example.
    Bruno.
    Attached Files Attached Files

  2. #2
    2 Star Lounger
    Join Date
    Mar 2010
    Location
    Tampa, FL, USA
    Posts
    114
    Thanks
    11
    Thanked 10 Times in 9 Posts
    With the range to process in "Sheet1", then
    1. In another sheet, for example "Sheet2", in location A1 enter:
    =Sheet1!A1/Sheet1!$F$1
    2. Select cell A1 in Sheet2
    3. Click on the "copy handle" in the lower right corner of the cell, and drag to cell E20, filling all the cells from A1 through E1 and down to A20 through E20.

    Each of these cells with have the original cells divided by the value in F1 on Sheet1.
    PJ in FL

  3. #3
    Star Lounger
    Join Date
    Jan 2010
    Location
    Queensland Australia
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you pjustice57, I will try that. The issue is a little more complicated than that. The workbook has 18 sheets (each sheet deal with a different power setting on a scope re ballistics). That is why each sheet will have different numerical values, all taken initially from the first sheet. I do not want to complicate the workbook with yet more sheets if I can avoid it. I would be surprised if there is not an easier manner in which to execute this command.
    Bruno.

  4. #4
    Star Lounger
    Join Date
    Jan 2010
    Location
    Queensland Australia
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes, this indeed works. I had the template book open and kept taking the altered values into the main book using a Paste Special for values only. A bit tedious but it works.
    I have worked with Excel for a long time but I don't think that I have ever had this issue before. I would appreciate it if someone has a simpler solution.
    Thank you pjustice57.
    Bruno

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Select Cell F1
    Edit - copy
    Select cells A1:F20
    Edit - Paste -special
    Divide

    Steve

  6. #6
    Star Lounger
    Join Date
    Jan 2010
    Location
    Queensland Australia
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Steve (old buddy from last year, co-ordinates), you have done it again. Simple and to the point.
    Many thanks.
    Bruno

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Bruno,
    You are very welcome. I am glad I can help.

    [In addition to coordinates (http://windowssecrets.com/forums/sho...s-from-numbers) don't forget Bell curves (http://windowssecrets.com/forums/sho...-a-spreadsheet)]

    Steve

  8. #8
    Star Lounger
    Join Date
    Jan 2010
    Location
    Queensland Australia
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Indeed, Bell curves, I had forgotten about that - you keeping a log?
    What I don't get is why the Range issue "escaped" me. I am sure I would have used your instructions myself years ago. Must be getting older.
    Perhaps I can work the ballistics results in with Bell curves - lots of maths in ballistics in terms of curvatures.
    Thanks Steve.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Quote Originally Posted by Bruno Terlingen View Post
    Indeed, Bell curves, I had forgotten about that - you keeping a log?
    No, I just noticed it when I looked up the link to coords on your user page...

    What I don't get is why the Range issue "escaped" me. I am sure I would have used your instructions myself years ago. Must be getting older.
    Yes the mind is the 2nd thing to go, I used to know what the 1st thing was, but I can't remember...

    [quote]Perhaps I can work the ballistics results in with Bell curves - lots of maths in ballistics in terms of curvatures.

    If the distribution is normal. If you use averages, it typically is, even though sometimes raw numbers are not...

    Thanks Steve.
    You are very welcome...

  10. #10
    Star Lounger
    Join Date
    Jan 2010
    Location
    Queensland Australia
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Just as an aside Steve: If I remember correctly, computers were "invented" to compute, and computers were initially used to work out artillery trajectories - these trajectories were, I believe, calculated by a heap of ladies before computational devices were introduced.
    I remember my father studying for some exam in the army and he had many different 'slide rules' and matrices to calculate different ranges for different artillery calibres.
    Unfortunately trajectories do not have a normal distribution because of all the different factors that are constantly changing while the projectile is in flight.

    Catch you later, Bruno.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If I remember correctly, computers were "invented" to compute, and computers were initially used to work out artillery trajectories
    I think one of the earlier electronic machines (Eniac?) was designed for this. The story about the women is that they were the "programmers" of Eniac, they did not do the calcs before Eniac. I recall reading that eniac for a long time was believed to be the original electronic computer, but after WWII files were declassified, the UK's Colossus, an electronic codebreaker, was discovered to be the earlier electronic machine

    Unfortunately trajectories do not have a normal distribution because of all the different factors that are constantly changing while the projectile is in flight.
    It should be more a 3-D normal distribution (more literally "bell-shaped"). But few things in life are truly "gaussian".

    Steve

Posting Permissions

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