Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 12
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 269
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 12
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copying formulas from cell to cell to cell to....... Tom Hardy Excel Discussion (Misc queries) 3 June 15th 06 03:29 PM
Looping in VB with cell ranges Freeman Excel Worksheet Functions 2 January 22nd 06 01:14 PM
copying cell names Al Excel Discussion (Misc queries) 12 August 11th 05 03:01 PM
Problem with formulas changing cell reference janicesweet Excel Discussion (Misc queries) 1 August 2nd 05 06:23 PM
Match function...random search? Les Excel Worksheet Functions 10 July 28th 05 11:54 AM


All times are GMT +1. The time now is 11:23 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"