How do I use a function as an array formula?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am attempting to use the function DEC2HEX over a range of values.

I know how to use an array formula by selecting the cells that I want for
the formula and then entering the formula, with array variables in the
formualbar. I then hit CTRL-SHIFT-ENTER and the array formula should be
entered in the column of interest.

However, when I use this function, DEC2HEX, I get a #VALUE error. I believe
that the function is expecting a numerical value and not a range index.

=DEC2HEX(A2:A4,4)

How do I work around this?

Thank You
Tom Cipollone
(e-mail address removed)
 
miner_tom said:
I am attempting to use the function DEC2HEX over a range of values.

I know how to use an array formula by selecting the cells that I
want for the formula and then entering the formula, with array
variables in the formualbar. I then hit CTRL-SHIFT-ENTER and the
array formula should be entered in the column of interest.

However, when I use this function, DEC2HEX, I get a #VALUE error. I
believe that the function is expecting a numerical value and not a
range index.

=DEC2HEX(A2:A4,4)

How do I work around this?

Thank You
Tom Cipollone
(e-mail address removed)
Click to expand...

Hi Tom,

This could be a version issue, but I do not have a DEC2HEX function.

I am using Excel 2000.

Alan.
 
Hi Tom,

This could be a version issue, but I do not have a DEC2HEX function.

I am using Excel 2000.

Alan.
Click to expand...

You need to install the Analysis Tool Pak AddIn
--ron
 
I am attempting to use the function DEC2HEX over a range of values.

I know how to use an array formula by selecting the cells that I want for
the formula and then entering the formula, with array variables in the
formualbar. I then hit CTRL-SHIFT-ENTER and the array formula should be
entered in the column of interest.

However, when I use this function, DEC2HEX, I get a #VALUE error. I believe
that the function is expecting a numerical value and not a range index.

=DEC2HEX(A2:A4,4)

How do I work around this?
Click to expand...

I'm not sure but it may be that DEC2HEX is not written to be used as an array
formula. What, exactly, are you trying to have as input and output?


--ron
 
Ron Rosenfeld said:
....
I'm not sure but it may be that DEC2HEX is not written to be used as
an array formula. What, exactly, are you trying to have as input and
output?
Click to expand...

Ron's correct that DEC2HEX can't accept multiple cell range or array
arguments nor return array results.

OP would need to use one formula for each cell in A2:A4. Select the current
array formula's range, make the topmost cell active, change the formula to

=DEC2HEX(A2,4)

and press [Ctrl]+[Enter] (no [Shift]!).

FWLIW, for a vertical array of decimal values between 0 and 65535, Rng, the
4-hexadecimal digit equivalents would be given by the array formula

=MID("0123456789ABCDEF",INT(Rng/16^3)+1,1)
&MID("0123456789ABCDEF",INT(MOD(Rng,16^3)/16^2)+1,1)
&MID("0123456789ABCDEF",INT(MOD(Rng,16^2)/16)+1,1)
&MID("0123456789ABCDEF",MOD(Rng,16)+1,1)
 
Ron Rosenfeld said:
You need to install the Analysis Tool Pak AddIn
--ron
Click to expand...

Hi Ron,

I already have that add-in installed, but I do not have a DEC2HEX
function available.

Are the Tool-Paks different in different versions of Excel perhaps?

Maybe it is a 2002 or 2003 function?

Alan.
 
I already have that add-in installed, but I do not have a DEC2HEX
function available.

Are the Tool-Paks different in different versions of Excel perhaps?
Click to expand...

I don't know
Maybe it is a 2002 or 2003 function?
Click to expand...

Maybe. But if memory serves, it was not new in 2002 (which is my current
version).

I can't explain it's absence in your version.


--ron
 
Hi
and the other ATP functions (e.g. WORKDAY)work for you?,.
If yes could you post the exact formula which has caused
this error.
 
Ron Rosenfeld said:
I can't explain it's absence in your version.
Click to expand...

It was corrupted - I had to remove it, reboot, and reinstall clean,
then it was okay.

Thanks for responding.

Regards,

Alan.
 
Frank Kabel said:
Hi
and the other ATP functions (e.g. WORKDAY)work for you?,.
If yes could you post the exact formula which has caused
this error.
Click to expand...

Hi Frank,

Following your post (above) I played around for a while since WORKDAY
was not working either.

Eventually, I fiured that the Add-in must be knackered, so I
uninstalled it, rebooted, and re-installed the add-in clean and it is
all okay now.

It must have gotten corrupted somehow.

Anyway, all is okay now, so thank you for your assistance,

Alan.
 
Back
Top