OK...this is probably a very simple answer, but I can't seem to find it...

I have a column of hexidecimal numbers (0x20, 0x40, etc.) that I want to
simply present the SUM of that colum in Hex. I've been unable to get the SUM
function to do this. What am I missing?

You may need a helper column where you convert from HEX to DEC (using
function HEXTODEC) and then sum that column. Convert back to HEX using
DECTOHEX function.

"GeeB" wrote in message
...
OK...this is probably a very simple answer, but I can't seem to find it...

I have a column of hexidecimal numbers (0x20, 0x40, etc.) that I want to
simply present the SUM of that colum in Hex. I've been unable to get the
SUM
function to do this. What am I missing?

On Fri, 8 Jul 2005 08:34:01 -0700, "GeeB"
wrote:

OK...this is probably a very simple answer, but I can't seem to find it...

I have a column of hexidecimal numbers (0x20, 0x40, etc.) that I want to
simply present the SUM of that colum in Hex. I've been unable to get the SUM
function to do this. What am I missing?

SUM adds numbers. HEX representations are strings so cannot be added by the
SUM worksheet function.

You could either write a custom VBA user defined function or use a helper
column:

With your data in A1:A5, in the above format, enter a formula

=hex2dec(MID(A1,3,255))

in B1 and drag down to B5.

In some cell, but the formula:

=DEC2HEX(SUM(B1:B5))


--ron

Thanks for the info...when I use your first example, I get a #NAME? error.

I have the Analysis Toolpak installed and enabled.


"Ron Rosenfeld" wrote:

On Fri, 8 Jul 2005 08:34:01 -0700, "GeeB"
wrote:

OK...this is probably a very simple answer, but I can't seem to find it...

I have a column of hexidecimal numbers (0x20, 0x40, etc.) that I want to
simply present the SUM of that colum in Hex. I've been unable to get the SUM
function to do this. What am I missing?

SUM adds numbers. HEX representations are strings so cannot be added by the
SUM worksheet function.

You could either write a custom VBA user defined function or use a helper
column:

With your data in A1:A5, in the above format, enter a formula

=hex2dec(MID(A1,3,255))

in B1 and drag down to B5.

In some cell, but the formula:

=DEC2HEX(SUM(B1:B5))


--ron

I have Excel 2000. No =hex2dec() in there, indeed it gives a #NAME error.
For myself I solved this with a simple DLL:
http://www.geocities.com/smplprgrsrc/files/HEX2DEC.zip

Probably not according to all conventions, but it works fine for me.




"GeeB" schreef in bericht
...
OK...this is probably a very simple answer, but I can't seem to find it...

I have a column of hexidecimal numbers (0x20, 0x40, etc.) that I want to
simply present the SUM of that colum in Hex. I've been unable to get the
SUM
function to do this. What am I missing?

The hyperlink is invalid. Are you sure it is correct?

"moi" wrote:

I have Excel 2000. No =hex2dec() in there, indeed it gives a #NAME error.
For myself I solved this with a simple DLL:
http://www.geocities.com/smplprgrsrc/files/HEX2DEC.zip

Probably not according to all conventions, but it works fine for me.




"GeeB" schreef in bericht
...
OK...this is probably a very simple answer, but I can't seem to find it...

I have a column of hexidecimal numbers (0x20, 0x40, etc.) that I want to
simply present the SUM of that colum in Hex. I've been unable to get the
SUM
function to do this. What am I missing?

On Fri, 8 Jul 2005 11:53:03 -0700, "GeeB"
wrote:

Thanks for the info...when I use your first example, I get a #NAME? error.

I have the Analysis Toolpak installed and enabled.


Copy and paste the exact formula you are using; and also copy/paste the exact
data it is working on.

Let's see if we can figure this out.


--ron

Thanks, that was quite stupid.
The link will work now.



"GeeB" schreef in bericht
...
The hyperlink is invalid. Are you sure it is correct?

"moi" wrote:

I have Excel 2000. No =hex2dec() in there, indeed it gives a #NAME error.
For myself I solved this with a simple DLL:
http://www.geocities.com/smplprgrsrc/files/HEX2DEC.zip

Probably not according to all conventions, but it works fine for me.




"GeeB" schreef in bericht
...
OK...this is probably a very simple answer, but I can't seem to find
it...

I have a column of hexidecimal numbers (0x20, 0x40, etc.) that I want
to
simply present the SUM of that colum in Hex. I've been unable to get
the
SUM
function to do this. What am I missing?

Sure, here's the basic column:

A1: 0x20
A2: 0x1
A3: 0x80

A5: =hex2dec(MID,A1,3,255))

After entering the formula, the #NAME? appears in A5

I've also tried removing the '0x' portion of the #'s with the same result.

I'm running Excel 2003

Thanks for your assistance.

Copy and paste the exact formula you are using; and also copy/paste the exact
data it is working on.

Let's see if we can figure this out.


--ron

I made a typo in my previous post...

This:
A5: =hex2dec(MID,A1,3,255))

Is actually this in my spreadsheet:
A5: =hex2dec(MID(A1,3,255))

Again, just a typo in the post. I used the correct formula in Excel and get
the error as noted.


"GeeB" wrote:

Sure, here's the basic column:

A1: 0x20
A2: 0x1
A3: 0x80

A5: =hex2dec(MID,A1,3,255))

After entering the formula, the #NAME? appears in A5

I've also tried removing the '0x' portion of the #'s with the same result.

I'm running Excel 2003

Thanks for your assistance.

Copy and paste the exact formula you are using; and also copy/paste the exact
data it is working on.

Let's see if we can figure this out.


--ron

On Fri, 8 Jul 2005 14:28:01 -0700, "GeeB"
wrote:

I made a typo in my previous post...

This:
A5: =hex2dec(MID,A1,3,255))

Is actually this in my spreadsheet:
A5: =hex2dec(MID(A1,3,255))

Again, just a typo in the post. I used the correct formula in Excel and get
the error as noted.

Well, that's why I suggested copy/paste rather than typing it in :-))

In any event, there is an MSKB article that may relate to your problem,
assuming your formulas are accurate. See

http://support.microsoft.com/default...b;en-us;291058

I would also suggest to install both the Analysis Tool Pak and the Analysis
Tool Pak-VBA.

Finally, see if HEX2DEC shows up in the function list, when you hit the Fx
button next to the formula bar.


--ron

Yea, I already had seen the KB and several others to no avail on a resolution.

The fx button doesn't show the function. Adding the APw/VBA pak doesn't help
either.

I finally installed Excel 2003 SP1 and that made it worse. I now get 'This
file is not in a recognizable format' message upon opening ANY Excel
worksheet.

Really bizarre behavior.

This is occurring on a test box (WS03 w/SP1 as a DC) that I'm writing
documentation for the undocumented switches of the subinacl utility using a
bare-bones Excel install (maybe something isn't installed that needs to
be??). So, I copied the Excel file over to my personal workstation where I'm
monitoring this post (hence, why I didn't copy/paste previously) and
everything works fine.

Oh well, since it is not an issue on my personal box where it works fine,
I'll just punt and not bother with it any more onthe test box. It was only
installed on the test box for the convenience factor to be able to document
while working.

Thanks to all for the help/suggestions!

Please keep in mind that you're talking about a Microsoft-Environment.
I gave you a .dll that works fine, simply because I know what you're dealing
with.

Instead of the crap below, just say: 'Thank you'.





"GeeB" schreef in bericht
...
Yea, I already had seen the KB and several others to no avail on a
resolution.

The fx button doesn't show the function. Adding the APw/VBA pak doesn't
help
either.

I finally installed Excel 2003 SP1 and that made it worse. I now get 'This
file is not in a recognizable format' message upon opening ANY Excel
worksheet.

Really bizarre behavior.

This is occurring on a test box (WS03 w/SP1 as a DC) that I'm writing
documentation for the undocumented switches of the subinacl utility using
a
bare-bones Excel install (maybe something isn't installed that needs to
be??). So, I copied the Excel file over to my personal workstation where
I'm
monitoring this post (hence, why I didn't copy/paste previously) and
everything works fine.

Oh well, since it is not an issue on my personal box where it works fine,
I'll just punt and not bother with it any more onthe test box. It was only
installed on the test box for the convenience factor to be able to
document
while working.

Thanks to all for the help/suggestions!

On Fri, 8 Jul 2005 15:20:02 -0700, "GeeB"
wrote:

So, I copied the Excel file over to my personal workstation where I'm
monitoring this post (hence, why I didn't copy/paste previously) and
everything works fine.

Oh well, since it is not an issue on my personal box where it works fine,
I'll just punt and not bother with it any more onthe test box. It was only
installed on the test box for the convenience factor to be able to document
while working.

Well, clearly there is something odd about the environment where it did not
work properly. But I'm afraid troubleshooting that is beyond me.

So I'm glad you've got it working on a more normal setup :-)


--ron

moi wrote:

Please keep in mind that you're talking about a Microsoft-Environment.
I gave you a .dll that works fine, simply because I know what you're
dealing with.

Instead of the crap below, just say: 'Thank you'.


I would think most people would be very leery of accepting a dll
from an individual on a newsgroup. Whats to say it doesnt contain
a trojan or some such thing? You'd be better off to just post the
source code to it and let anyone who wants it to compile it themselves.
There was a time you could hand out executeables and such and people would
gladly accept them, but in todays world, 99% of the time its going to be
something evil. Dont take me wrong, I'm not saying your doing anything
untoward and in fact I think its pretty slick you came up with a dll to
solve this so fast. I tend to solve things in the same kind of way, I've
got a slug of C/C++ stuff i wrote to solve various problems I had
(utilites, cgi etc).
Eric

Actually, the dll did not help me as it acts more like a standalone
pre-defined scientific calculator and that's not what I needed. Additionally,
due to the simplicity needed and that my spreadsheet will distributed to
others (including internally to MS), the dll does nothing for me as I'm not
going to distribute it or provide a link to an additional dll that's not part
of Excel.

I don't think 'explaining' the scenario is 'crap'. I did say thanks to all
(which means u too). You apparently don't work much in 'customer service
oriented' support (or simply a myopic picture of it)...details help, not hurt
those who are trying to assist.