• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Mass formula to cells

M

markkara

New Member
Hey Guys


I have an issue which is bugging me and something i dont want to do manually.


In the attached document, i need the bolded subtotal in column E to be multiplied by the pertinent cells in column D i.e. cell E7 is 194.94 * D2 = 194.94, cell E7 is 194.94 * D3 = 194.94 and so on.


My example doesnt contain many rows as yet, but i plan on pulling data with 10k plus rows and i dont want to do this manually with a drag.


Is their anyway i can apply a 'mass formula' for this situation?


Any help is appreciated.


Thanks


http://www.sendspace.com/file/xv8oj2
 
Hi, markkara!

And where do you want to store those values? And what do you want to do when you reach D6, go on? Or another Subtotal cell (rows 14, 20, ...)?

Could you please describe the output desired? Thanks.

Regards!
 
Hi, markkara!

The subtotal rows, 7-14-20, are manually added or automatically using Data tab, Schema group, Subtotal button?

And how will the 10K+ rows are going to be added? You'll delete Subtotals, copy/import data, sort, add subtotals and then write mass-formulas and copy them to whole range?

Regards!
 
What ive done is pulled a report from our database and ive applied a subtotal to columns A-D.


Column E, only on the sub total line for each change in value in column A is just a simple division.


What i want is the proceeding cells in column to be auto filled with the formula = $E$7 * D6.


The issue i have is that the locked number with the $ will need to be updated at each subtotal point, however i dont really want to manually do this due to the time it would take.


The example provided doesnt contain the 10k lines, but i plan on running a similar type report that will have this amount of lines and im not really keen on running this formula manually at each subtotal interval for the blank columns that proceeding the division formula in column E.


Hope that makes sense.
 
The may sound funny, but why are you putting in subtotal lines? From your example, it looks like you are using the calculation in D to sum the Ft Print and in E to get an average $/Ft.


These calculations could be done with array formulas or maybe a pivot table without adding the subtotal lines. Just a thought.
 
I did try the pivot option to bring in the subtotals and that worked, but i still ran into the issue of trying to work the calculation into the pivot.


When i did the calculation in the pivot, it only worked correctly for the 'subtotals' and not the other cells.


Can you expand on any particular array forumlas that could work?
 
Hi ,


You can use either of the following formulae :


1. =IF(ISERROR(INDEX(A2:$D$754,MATCH(A2&" "&"Total",A3:$A$754,0)+1,3)),C2/D2,INDEX(A2:$D$754,MATCH(A2&" "&"Total",A3:$A$754,0)+1,3)*D2/INDEX(A2:$D$754,MATCH(A2&" "&"Total",A3:$A$754,0)+1,4))


or


2. =IF(ISERROR(OFFSET(A2,MATCH(A2&" "&"Total",A3:$A$754,0),2)),C2/D2,OFFSET(A2,MATCH(A2&" "&"Total",A3:$A$754,0),2)*D2/OFFSET(A2,MATCH(A2&" "&"Total",A3:$A$754,0),3))


Enter this in cell E2 , and copy it downwards.


I have used D754 as an arbitrary end address for your data ; change this to whatever is your actual range end.


You need to do this after you have introduced the subtotals , because the formulae are looking for the word "Total" in column A , which comes from introducing subtotals.


Narayan
 
You must log in or register to reply here.
Back
Top