I've never had this problem before but I'll do my best to explain it.
I was multiplying 2 ranges in an formula to get a cell's value. The first was a set of user inputs and the second was an array of constants. Recently I needed to add another field in the middle of the user inputs which subsequently broke the range.
So I had:
Now I inserted a row so Excel changed it toCode:{=SUM(H12:H21*B141:B150)}
Except I now need to exclude H13. I have absolutely no idea how to do this properly. Any help would be greatly appreciated.Code:{=SUM(H12:H22*B141:B150)}
Last edited by marksimpson884; 12-22-2008 at 10:02 PM.
Regular formula: =H12 * B141 + SUMPRODUCT(H14:H22, B142:B150)
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Alright, thanks that's half of it I just realized. I have another where its in an if statement....
If it were possible to break up a range this would be so much easier...Depending on how hard this one is I might just give up and rewrite the entire workbook using vlookup and a hidden sheet.Code:{=SUM(IF(H12:H22>(B8:B17+B5*E132:E141),(B8:B17+B5*E132:E141),I12:I22)*B141:B150)}
Umm ... why not insert the column to the left of the range instead of in the middle?
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Well I'm thinking about that. More likely what I'll do is insert it before the range. There was a specific order to these (ascending in cost) but I guess I can sacrifice that for having a workbook that isn't a nightmare to update.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks