Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Naming cell ranges, copying formulas for a range & nesting "IF" fu
Please bear with me! I'm brand new to both Excell and spreadsheets in general.
1) I am trying to name a column range, as in "pay_amt=(C8:C12)" I've gone through the; InsertName(Create | Define |Apply) processes to create this name range, but to no avail. Two of the ranges I've tried to build are (cur_pay and sup_pay_amt) 2) Part two of the problem is a driviative of #1. I want use a nested "IF" function to evalue the column range named "cur_pay". I set up the function like this: =IF(cur_pay=(68),sup_pay_amt=(68-10),=IF(cur_pay=(136),sup_pay_amt=(136-20)......... I've also tried using the function drop-down menu to create the test and post the dollar amount. So far, I've spent 3 days trying to figure this two realted (for me anyway) issues. I need to make five tests and if they all fail, I need to post an "Invalid Entry" text message as the final value_IF_False I've worn out both the help section and the Excell for dummies. Appearently, I now qualify as a dummy! -- Don |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Naming cell ranges, copying formulas for a range & nesting "IF" fu
Don, to give a column, do this:
1. Click on the letter above the column to highlight the column. 2. Click on Insert on the menubar, then Name, then Define. 3. Look for the name you intend to use for your column. If it already exists, delete it, reselect the column and repeat steps 1 and 2. 4. Type in the name you intend to use. It must begin with an alphabetic character. You may not use spaces or special characters in the name. Use only characters A-Z, a-z, 0-9 and _. 5. Click OK. Repeat to name the second column, third column, however many. Go to an empty column where you want to do use your formula. Type in your formula and press Enter. Drag this formula down the column. Hope this helps, James DonF wrote: Please bear with me! I'm brand new to both Excell and spreadsheets in general. 1) I am trying to name a column range, as in "pay_amt=(C8:C12)" I've gone through the; InsertName(Create | Define |Apply) processes to create this name range, but to no avail. Two of the ranges I've tried to build are (cur_pay and sup_pay_amt) 2) Part two of the problem is a driviative of #1. I want use a nested "IF" function to evalue the column range named "cur_pay". I set up the function like this: =IF(cur_pay=(68),sup_pay_amt=(68-10),=IF(cur_pay=(136),sup_pay_amt=(136-20)......... I've also tried using the function drop-down menu to create the test and post the dollar amount. So far, I've spent 3 days trying to figure this two realted (for me anyway) issues. I need to make five tests and if they all fail, I need to post an "Invalid Entry" text message as the final value_IF_False I've worn out both the help section and the Excell for dummies. Appearently, I now qualify as a dummy! -- Don |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Naming cell ranges, copying formulas for a range & nesting "IF" fu
I'm using Excel 2003 -
As far as (1) goes, I went through Insert-Name-Define, typed in the name, i.e. cur_pay, and made sure the refers to box reference the correct range - i.e. =Sheet1!$C$8$:$C$12, and then clicked Add. Does that not work for you, and if not, what error/response are you getting? As far as (2), it would seem to me that you're failing to the function you want to apply to the defined range. cur_pay =(68) would not be a valid condition, it would be the same as saying IF (C8:12)=(68) -- Also, the syntax of your IF statement as presented appears wrong - you should have IF(logical test value, value if true, value if false) Assuming that what you want is the SUM of C8:C12, then the statement should be IF SUM(cur_pay)=68,value if true, value if false) DonF wrote: Please bear with me! I'm brand new to both Excell and spreadsheets in general. 1) I am trying to name a column range, as in "pay_amt=(C8:C12)" I've gone through the; InsertName(Create | Define |Apply) processes to create this name range, but to no avail. Two of the ranges I've tried to build are (cur_pay and sup_pay_amt) 2) Part two of the problem is a driviative of #1. I want use a nested "IF" function to evalue the column range named "cur_pay". I set up the function like this: =IF(cur_pay=(68),sup_pay_amt=(68-10),=IF(cur_pay=(136),sup_pay_amt=(136-20)......... I've also tried using the function drop-down menu to create the test and post the dollar amount. So far, I've spent 3 days trying to figure this two realted (for me anyway) issues. I need to make five tests and if they all fail, I need to post an "Invalid Entry" text message as the final value_IF_False I've worn out both the help section and the Excell for dummies. Appearently, I now qualify as a dummy! -- Don |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Naming cell ranges, copying formulas for a range & nesting "IF
Tom,
It seems that I have the names inserted properly now, however, my columns start at row 40. When I check the names, the column name of "cur_pay" shows the Refers To as "=$James$!$D40:$D$300. Which is the correct range, however, if I select any different cells within the range, I just see the Column and Row, (D41). I don't see the name of the rqange in the name block, is that normal? On my inserted "IF" function, I am trying a nested "=IF" function. The function is supposed to check a preceeding column and evaluate the variable, "ck_amt". "ck_amt" should equal one of five possible values or it steps on to the next IF function within the nested IFstatement. =IF(ck_amt=(68)," 58 | 10 ", =IF(ck_amt=(136)," 116 | 20", +IF (ck_amt=(138)," 116 | 20 " ect. ect. Don, to give a column, do this: 1. Click on the letter above the column to highlight the column. 2. Click on Insert on the menubar, then Name, then Define. 3. Look for the name you intend to use for your column. If it already exists, delete it, reselect the column and repeat steps 1 and 2. 4. Type in the name you intend to use. It must begin with an alphabetic character. You may not use spaces or special characters in the name. Use only characters A-Z, a-z, 0-9 and _. 5. Click OK. Repeat to name the second column, third column, however many. Go to an empty column where you want to do use your formula. Type in your formula and press Enter. Drag this formula down the column. Hope this helps, James DonF wrote: Please bear with me! I'm brand new to both Excell and spreadsheets in general. 1) I am trying to name a column range, as in "pay_amt=(C8:C12)" I've gone through the; InsertName(Create | Define |Apply) processes to create this name range, but to no avail. Two of the ranges I've tried to build are (cur_pay and sup_pay_amt) 2) Part two of the problem is a driviative of #1. I want use a nested "IF" function to evalue the column range named "cur_pay". I set up the function like this: =IF(cur_pay=(68),sup_pay_amt=(68-10),=IF(cur_pay=(136),sup_pay_amt=(136-20)......... I've also tried using the function drop-down menu to create the test and post the dollar amount. So far, I've spent 3 days trying to figure this two realted (for me anyway) issues. I need to make five tests and if they all fail, I need to post an "Invalid Entry" text message as the final value_IF_False I've worn out both the help section and the Excell for dummies. Appearently, I now qualify as a dummy! -- Don |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying formulas from cell to cell to cell to....... | Excel Discussion (Misc queries) | |||
Looping in VB with cell ranges | Excel Worksheet Functions | |||
copying cell names | Excel Discussion (Misc queries) | |||
Problem with formulas changing cell reference | Excel Discussion (Misc queries) | |||
Match function...random search? | Excel Worksheet Functions |