Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUM of Hex?
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
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? |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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? |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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 |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
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! |
#13
|
|||
|
|||
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! |
#14
|
|||
|
|||
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 |
#15
|
|||
|
|||
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 |
#16
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|