Need One Formula to Divide the Sum of One Column By Another

Greetings,

I can do this the 'easy' way by summing two separate ranges in two separate cells and dividing one by the other; however, I am trying to build a tool that can be copied and pasted into a bunch of different uniform excel documents that calculates total percentages.

So, for instance, in the attached example, for each program year I need to add up the number of eligible individuals with positive outcomes for reading writing, math, and workforce prep and then divide that number by the total number of eligible individuals for the same rows in the eligible individuals column.

Essentially, I need to know the overall percentage of students for a program that achieved a positive outcome; and since this is meant to be a tool that can be copied and pasted into existing excel workbooks, I would love it if I could do this entire operation with a single formula in a single cell as to not create a (potential) confusing mess for the end user.

Any help would be appreciated!

Advice

1. I would suggest to not using merge cell
2. sum formula, for example

at cell D10

Formula:

Please Login or Register  to view this content.

Part of the problem is the format in which the data was submitted--exactly like the sample I already included. We have two overarching columns broken down further into program years. The first is the 'number of eligible individuals enrolled' and the second is 'number of eligible individuals with demonstrated improvement.'

Then the rows correspond to subject areas: reading, writing, mathematics, English language acquisition, civics + citizenship education, and workforce prep.

The criteria for 'demonstrating effectiveness' that I alluded to earlier, are whether or not the applicant achieved a particular success rate for each year. The complication here is that I cannot just use the sums in the totals row for this calculation because there are separate criteria for 'regular students' and 'English as a second language' students; and the way the data was submitted provides information on both in the same column.

This is why I need to sum the values in the rows 4, 5, 6, and 9 in column HI and then divide that by the sum of the values in the same rows under DE (as this represents the 'regular' students.) Eventually I will have to do the same for the ESL student data; however, once I figure out how (if it can be done) to do this whole operation with a single formula for the 'regular' students, I can just copy the format for the ESL students.

Does this make sense?

Fake Edit

See the sample 3 attachment. My goal is to calculate the percentage found at R10 without first having to sum the ranges in separate cells (like I did here in cells P10 and Q10).

Really, thanks for the help in general, and for the help in walking me through how to ask the question.

You mean like this?

at any cell, maybe D12

Formula:

Please Login or Register  to view this content.

Originally Posted by Sambo kid

there is no reason you need the sums in P10 and Q10 the way you have them. As biex noted, merged cells are not good to use, they can confound users and formulas.
The formula you need in D10 (that will be the address it will be in if you look to the far left of the formula bar) is simply =SUM(D4:D9) and it will sum the numbers in column D and exclude the text. Same for H10.
Now, you can simply use =H10/D10 to get your goal.
hope that helps.

Unfortunately this is what it is. I did not make the original template for the providers to submit with merged cells, and all of the applications have been submitted; therefore, there is no going back on the fact that I am going to have to work with, or around, the merged cells.

Originally Posted by Sambo kid

there is no reason you need the sums in P10 and Q10 the way you have them. As biex noted, merged cells are not good to use, they can confound users and formulas.
The formula you need in D10 (that will be the address it will be in if you look to the far left of the formula bar) is simply =SUM(D4:D9) and it will sum the numbers in column D and exclude the text. Same for H10.
Now, you can simply use =H10/D10 to get your goal.
hope that helps.

Haven't looked at the other replies this morning yet, but yes, I understand I could do that... except I entered that text just to show that I needed to exclude those cells. The real data has numeric values in those cells with the "excluded" text.