How do I use a function as an array formula?

[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)

 

[Alan]

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)

Hi Tom,

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

I am using Excel 2000.

Alan.

 

[Ron Rosenfeld]

Hi Tom,

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

I am using Excel 2000.

Alan.

You need to install the Analysis Tool Pak AddIn
--ron

 

[Ron Rosenfeld]

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?

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

 

[Harlan Grove]

....
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'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)

 

[Alan]

You need to install the Analysis Tool Pak AddIn
--ron

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.

 

[Frank Kabel]

Hi alan
are you using an English Analysis Toolpak Add-in?

 

[Alan]

Hi alan
are you using an English Analysis Toolpak Add-in?

Definately!

Alan.

 

[Ron Rosenfeld]

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?

I don't know

Maybe it is a 2002 or 2003 function?

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

 

[Frank Kabel]

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.

 

[Alan]

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

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

Thanks for responding.

Regards,

Alan.

 

[Alan]

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.

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.