• IMPORTANT: Please note that nobody on this forum should be seeking from or providing advice to those who are not competent and / or trained and qualified in their field (local laws permitting). There is a discussion thread on this global industry-wide matter HERE. This also has more information about the warning with regards to sharing electrical advice in some countries. By using this forum you do so in agreement to this.

Discuss excel formula Help in the Business Related area at ElectriciansForums.net

Welcome to ElectriciansForums.net - The American Electrical Advice Forum
Head straight to the main forums to chat by click here:   American Electrical Advice Forum

marc8

-
Reaction score
164
hi all small formula issue can anyone help got the basics can add, multiply, ect copy columns, but need a multiple column (sum) entry.

Row
a item £ sorted
b total £ sum of h-P (sorted) H-P x a = B all good

Column
1-200 some headings but overall

so I need
H1 - H200 column H x A But for the column in one cell H201.

so above I am looking to repeat a sum which is total amount of items x cost x 199 lines into cell 201.
the spreadsheet has already taken the amount across H-P total amount of units & X the item amount & put into a cell.

example
(A1 = £1.20) H1 20 I1 20 J1 0 So On, B1 = £48.00
repeating lines same rows different amounts
column H201 total would = H1 x A1 =£24.00, need to add 2-199 to H201?

any help would be appreciated
 
sort gone a bit wrong somehow

my sum =sum(E11*I11)*(I11:I225)
So Looking for the total cost first part & to repeat for second part.
if I do the first part of sum fine & if I do the second part of sum it is correct 178 items but don't need the 178 just 1st part of sum repeated to I11:I225
 
Last edited:
Upvote 0
I would go for a simpler approach. Array formulas can be a PITA.

You can hide columns, so you can create the formula =H1*A1 in say column M, copy and paste that using the formula painter (there is a little nobble appear on the bottom right corner of a highlighted cell, drag that and the formula in the cell will be copied to the area you select while dragging). Then, select column M by clicking the heading cell with M in it, right click and select Hide.

Then set H201 to the formula =SUM(M1:M200).

Yes, it may take longer than entering a single formula, but it's less likely to cause you headaches. I've always had problems with array formulas and normally just use the hidden column/row approach.
 
Upvote 0
I would go for a simpler approach. Array formulas can be a PITA.

You can hide columns, so you can create the formula =H1*A1 in say column M, copy and paste that using the formula painter (there is a little nobble appear on the bottom right corner of a highlighted cell, drag that and the formula in the cell will be copied to the area you select while dragging). Then, select column M by clicking the heading cell with M in it, right click and select Hide.

Then set H201 to the formula =SUM(M1:M200).

Yes, it may take longer than entering a single formula, but it's less likely to cause you headaches. I've always had problems with array formulas and normally just use the hidden column/row approach.


Hi Sparkychick
thanks forgive my novice approach but how ?
 
Upvote 0
Hi All

Managed to get what I need But slow =Sum(E11*I11,E12*I12,E13*I13,E14*I14,E15*I15,E16*I16,E17*I17,E18*I18,E19*I19,E20*I20,E21*I21,E22*I22,E22*I22,E23*I23)
but must be an easier way to I225
 
Upvote 0
It's slow, because a single change triggers a recalculation of that entire formula.

I've attached a sample of how I would go about it. If I didn't want column N visible, I'd hide it a bit like column O. To unhide a column, select the columns on either side, right click and select Unhide :)
 
Upvote 0
Yes I think Sparky Chick has the simplest solution.

Have each calculation for E11*I11.... in cells a separate column and sum that column at the bottom.
Although i do think I may be missing something from your explanation.
 
Upvote 0
many thanks for help
Column 4 now does total numbers of units used. Column 5 does unit cost total.
so our row reads
1A unit cost, 1B total costs, 1C plot total units, 1D Plot Value of units.
1B= 1Ax1C
1D= 1Ax1C
225D=1D:224D Gives me overall for flat.
this is not something I required but had to be filled in for Tender, when sheet arrived with me it asked for 1A,1B & 1D.
only problem was it just gave me a load of figures of which I had to keep Dividing to reference a total number of units to check my costs. this way I can see total number of units allowed & the cost for each & total for the plot & overall of all plots.

I have not used excel spread sheet in over a year as have already got costing software.

thanks for help not sure if the information above made sense hope it did & Thanks.
 
Upvote 0

Reply to excel formula Help in the Business Related area at ElectriciansForums.net

This website was designed, optimised and is hosted by Untold Media. Operating under the name Untold Media since 2001.
Back
Top