how to apply a formula to hundreds of rows

**Malkey** · 06-09-2012, 11:37 AM

Hi
I have a table of Numbers. It consists of 10 columns and 1600 rows. The columns are for years and the rows are for the company names
They are 800 companies Each company has two rows of numbers ( Item A and Item B )
I want to divide item A by Item B for each company

I did that for the first company ( i divided the first row by the second row)

My question is:: How can I apply that to the rest of the table

There are still 1598 rows

I really urgently need your help guys. It is not only one table I have a number of tables for which I will have to use similar formulas

**thameem127** · 06-09-2012, 11:40 AM

Please send attached file...

Thanks
Thameem

**Malkey** · 06-09-2012, 11:45 AM

thanks for ur reply
I am dealing with my project data . I have signed a declaration that I will not release or expose these data without a permission
Sorry for that. If u need more clarification for my question let me know

I look forward to the answer

**Richard Buttrey** · 06-09-2012, 11:52 AM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

**Malkey** · 06-09-2012, 11:57 AM

All done.. Hope i will get a solution to my problem now^__^

**oeldere** · 06-09-2012, 02:49 PM

If that's your problem, you get better help if you post an example of your workbook (without confidentional information).

**Malkey** · 06-10-2012, 08:25 AM

Thanks Guys
I have created an example

In this example there are 5 companies Each company has two rows of numbers for a number of years
I need the divide row 1 by row 2 for each company ( The results are to be shown either under the table or on the right hand side)

Since my real table is extremely large I need to know how to make this function more easily

For Example in Company 1 : I want to divide Debt by Equity Then I need to copy this to the rest of the table

I hope my inquiry is clearer now

Waiting for your response guys

**Richard Buttrey** · 06-10-2012, 08:29 AM

Hi,

Are you wanting to sum all the years for a company and then calculate the debt:equity ratio, or are you wanting this for each company for each year?

**Malkey** · 06-10-2012, 08:34 AM

Hi

I want to divide the debt by Equity for Each Company Each year

Later on, I will take the average . My concern now is how to divide debt by Equity for each company each year as my real table is EXTREMELY HUGE

**Richard Buttrey** · 06-10-2012, 09:32 AM

Hi

Assuming the equity row always follows a debt row, in B2 and copied down, for the year 2000

=IF(RIGHT(TRIM(A2),4)="debt",C2/C3,"")

note that there is a trailing space in A6 hence the incorporation of the Trim() function. If all A cells end in just "Debt" than you can remove the trim.

And similarly for other years.

**Malkey** · 06-10-2012, 09:54 AM

Hi
You might be surprised that I did not get it

I am a new excel user ( In fact I had to use it otherwise I would not have worried about it) ^--^

Can you please explain to me step by step

Sorry for the inconvenience. I was really anxious but when I found this forum I was really relieved

**MrShorty** · 06-10-2012, 10:35 AM

If I understand your question correctly, it seems to me you need a quick lesson in relative versus absolute references.

If you look at Richard Buttrey's formula, you will note that the actual division is done by the formula C2/C3. These are relative references, meaning that if you copy and paste them elsewhere, the references will update so they will always refer to the same cells relative to the pasted cell. So, if this formula is in row 2, and you copy the formula to the clipboard then paste it down the column into the other rows, the references will update in each pasted cell as C3/C4, C4/C5, C5/C6, and so on. If you put a dollar sign in a reference (for example C$2/C3), then that part of the reference becomes absolute and won't change. This formula copies as C$2/C4, C$2/C5, and so on.

Having got it to work for the first row, it should be as simple as copying the formula down to get it to work for all rows.

how to apply a formula to hundreds of rows

LinkBack

Thread Tools

Rate This Thread

Display

how to apply a formula to hundreds of rows

Re: Urgent

Re: Urgent

Re: Urgent

Re: Apllying a formula to hundreds of rows

Re: Apllying a formula to hundreds of rows

Re: Apllying a formula to hundreds of rows

Re: Apllying a formula to hundreds of rows

Re: how to apply a formula to hundreds of rows

Re: how to apply a formula to hundreds of rows

Re: how to apply a formula to hundreds of rows

Re: how to apply a formula to hundreds of rows

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions