Working with “ranges” within Excel

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Select Cell F1
Edit – copy
Select cells A1:F20
Edit – Paste -special
Divide

Steve

Reply | Quote

Steve (old buddy from last year, co-ordinates), you have done it again. Simple and to the point.
Many thanks.
Bruno

Reply | Quote

Bruno,
You are very welcome. I am glad I can help.

[In addition to coordinates (http://windowssecrets.com/forums/showthread//128777-Creating-degree-values-from-numbers) don’t forget Bell curves (http://windowssecrets.com/forums/showthread//125704-How-do-I-create-%22bell-curves%22-in-a-spreadsheet)]

Steve

Reply | Quote

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.

Reply | Quote

Indeed, Bell curves, I had forgotten about that – you keeping a log?[/quote]

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…

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…

Reply | Quote

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.

Reply | Quote

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

Reply | Quote