use non-adjacent cells for an argument

  • Thread starter Thread starter sue
  • Start date Start date
Don said:
homework?
=sum(a1,b4,x5)
Click to expand...
Don,

What I meant was how do I (if it is possible) use a references like "C2, F4:G8, H10" as one argument
for a function that takes several required arguments, like NPV or PV. I do know that the delimiter
for function arguments is a "," so this won't work.

and...No this isn't homework...unless you mean that I am working at home?
 
Hi Sue!

Select C2, F4:G8, H10 [Use the Ctrl button to select discontiguous
cells]
Type MyRange in the name box [to left of formula bar]
Enter

Now you can use:

=SUM(MyRange)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Norman said:
Hi Sue!

Select C2, F4:G8, H10 [Use the Ctrl button to select discontiguous
cells]
Type MyRange in the name box [to left of formula bar]
Enter

Now you can use:

=SUM(MyRange)
Click to expand...
So using a named reference is the only alternative...I was hoping that wasn't so...

Thanks
 
what function are you using?

Maybe you want to wrap your non-contiguous areas in parentheses:

=yourfunction((A1:B4,C9:D9),E99)

Some functions allow this.


Norman said:
Hi Sue!

Select C2, F4:G8, H10 [Use the Ctrl button to select discontiguous
cells]
Type MyRange in the name box [to left of formula bar]
Enter

Now you can use:

=SUM(MyRange)
Click to expand...
So using a named reference is the only alternative...I was hoping that wasn't so...

Thanks
Click to expand...
 
Hi Sue or Scott or Whomever!

Re: "So using a named reference is the only alternative...I was hoping
that wasn't so.."

It might help if you give more details of what you are doing.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Norman said:
Hi Sue or Scott or Whomever!

Re: "So using a named reference is the only alternative...I was hoping
that wasn't so.."

It might help if you give more details of what you are doing.
Click to expand...
Sorry, this is Sue, but for some reason I can't find this thread on my newsgroup client??? So, I'll
be Scott for now since this is his machine.

I wanted to use this type of reference with the NPV function...I am going to try Dave's idea.

Sorry for the late reply...wonder what happened to that thread?

Sue
 
Hi Scott!

NPV accepts (eg)

=NPV(E1,A1:A3,C4:C6)

Or if I select A1:A3 and C4:C6 and name them "MyRanges"

=NPV(E1,MyRanges)


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top