Naming cell ranges, copying formulas for a range & nesting "IF" fu

[DonF]

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; Insert>Name>(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!

 

[Zone]

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

 

[Seahawk60B]

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]

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.