How to use a named column in Excel formulas

0 votes

In Excel, I am aware of how to create a named range.

A formula is in the last cell of a spreadsheet that has several columns acting as parameters. Each row has a separate set of data, which is repeated numerous times, and the formula is changed to correspond to the appropriate row index.

However, the formula looks like this (three rows worth):

=G2*(10*D2 + 20*E2 + 5*F2)
=G3*(10*D3 + 20*E3 + 5*F3)
=G4*(10*D4 + 20*E4 + 5*F4)

I would like to use named ranges, but I can't find a way to do something like

=Count * (10*var1 + 20*var2 + 5*var3)

Where count, var1, var2, and var3 are updated automatically to reflect the specific row's column. For each cell, I can make a named range, but that is useless. I am able to name the range in the column, but I am unable to figure out how to include an offset in the calculation.

Additionally, because the entire purpose of this is readability, it probably doesn't help much if the function call turns out to be some horribly convoluted formula.

Oct 24 in Others by Kithuzzz
• 20,660 points
38 views

1 answer to this question.

0 votes

Let's say I have the following figures put up in rows 2 to 4 and columns D to F:

    D    E    F    G
2   10   15   20
3   1    2    3
4   20   30   40


Let's say I want column F to be input3, column E to be input2, and column D to be input1.

In Insert > Name > Define...

input1 RefersTo =OFFSET(Sheet1!$D$2,0,0,COUNT(Sheet1!$D:$D),1)
input2 RefersTo =OFFSET(Sheet1!$E$2,0,0,COUNT(Sheet1!$E:$E),1)
input3 RefersTo =OFFSET(Sheet1!$F$2,0,0,COUNT(Sheet1!$F:$F),1)

Now if I write my formula in column G as follows I should get the correct answers:

G2 =(10*input1+20*input2+30*input3) // 1000
G3 =(10*input1+20*input2+30*input3) // 140
G5 =(10*input1+20*input2+30*input3) // 2000
answered Oct 24 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

How do I transpose a column to a matrix in Excel?

With data in column A, pick some cells ...READ MORE

answered 2 days ago in Others by narikkadan
• 37,660 points
10 views
0 votes
1 answer
0 votes
1 answer

How to create a drop-down in excel with custom values

You can accomplish that using code rather ...READ MORE

answered Sep 25 in Others by narikkadan
• 37,660 points
56 views
0 votes
1 answer

How to use the INDIRECT function in Excel

Try this: =INDEX(INDIRECT($A$1&"!$1:$1048576"),MATCH($B$1,INDI ...READ MORE

answered Sep 29 in Others by narikkadan
• 37,660 points
66 views
0 votes
1 answer

Excel COUNTIF formula

Please see MS Excel: COUNTIF Function (WS) You should ...READ MORE

answered Sep 29 in Others by narikkadan
• 37,660 points
52 views
0 votes
1 answer

Is there a maximum number of formula fields allowed in Excel (2010)

See http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx for limits on specs it doesn't indicate ...READ MORE

answered Sep 30 in Others by narikkadan
• 37,660 points
53 views
0 votes
1 answer

Multiplying cells and sum then for a range

Use SUM() as an Array formula:  =SUM(IFERROR((AB22:AB1100 = ...READ MORE

answered Oct 2 in Others by narikkadan
• 37,660 points
62 views
0 votes
1 answer

INDEX formula in Excel, Top 10, repeats previous value

Try this formula in cell W4: =IF(V3=V4,INDEX(INDIRECT("I"&MATCH(W3,I:I,0)+1&":I26"),MATCH(V4,INDIRECT("R"&MATCH(W3,I:I,0)+1&":R26"),0)),INDEX($I$2:$I$26,MATCH(V4,$R$2:$R$26,0))) The calculation ...READ MORE

answered Oct 8 in Others by narikkadan
• 37,660 points
47 views
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24 in Others by narikkadan
• 37,660 points
57 views
0 votes
1 answer

How to find a value in an excel column by vba code Cells.Find

Just use: Dim Cell As Range Columns("B:B").Select Set cell = ...READ MORE

answered Nov 17 in Others by narikkadan
• 37,660 points
17 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP