DiyLightAnimation
Fun => The Porch => Topic started by: NavyGator on February 09, 2012,
-
So, while working on a spreadsheet for handling COOP's today I came across something that was interesting and scary all at the same time.
The maximum number of characters that can be in an Excel (2010) formula are 8,192.
The interesting part is that I now know the limit. The scary part is I had to break my formula up into three parts. Hehehe.
Let the humor flow forth....
-
That is one complicated formula!
Sent from my Motorola Atrix2
-
Have you looked to shrink your reference points to single or double letters? That may help shrink it a bit.
-
3 parts? heh simple arithmetic... lol
-
Have you looked to shrink your reference points to single or double letters? That may help shrink it a bit.
I thought of a different way yesterday but I figured I was so far down this path that I might as well finish it off to see the feasibility of the whole spreadsheet.
-
3 parts? heh simple arithmetic... lol
Right....again... <fp.
-
So its two Pixelnet Universes
Is the formula in binary?
Rick R.
-
i'm curious to see this formula ;D
-
Might I recommend you modify the formula from
=A1+A2+A3+A4+A...
to
= sum(A1:A5000)
;D
-
So its two Pixelnet Universes
Is the formula in binary?
Rick R.
It is kind of ironic that it is 2 PixelNet universes.
-
i'm curious to see this formula ;D
I probably shouldn't do this...but....here it is. Well 1/3 of it anyways.
It is basically nested if statements. In hind sight there was a couple of other options to do this.
-
So its two Pixelnet Universes
Is the formula in binary?
Rick R.
It is kind of ironic that it is 2 PixelNet universes.
etherdongle, and you've got room to grow!
-
Not sure what you are trying to do, but I think that type of coding would be more appropriate as a virtual basic macro that you call rather than a formula embedded in a cell. If it doesn't work the first time, editing an debugging will be a nightmare.
The industry average is around 40 errors per thousand lines of code (before reviews). At 8000 characters, you probably have 4 errors... In each third... (I figured using 80 characters per line, which is really a long line)
RM
-
Not sure what you are trying to do, but I think that type of coding would be more appropriate as a virtual basic macro that you call rather than a formula embedded in a cell. If it doesn't work the first time, editing an debugging will be a nightmare.
The industry average is around 40 errors per thousand lines of code (before reviews). At 8000 characters, you probably have 4 errors... In each third... (I figured using 80 characters per line, which is really a long line)
RM
I was able to build it in sections, and there is some repetition in the code so it wasn't too bad to write. It just looks horrendous.
I have never used macros yet. I am looking at some to see how they work to try and figure them out.
If I am not mistaken, don't macros also cause security concerns?
Rich
-
So, while working on a spreadsheet for handling COOP's today I came across something that was interesting and scary all at the same time.
The maximum number of characters that can be in an Excel (2010) formula are 8,192.
The interesting part is that I now know the limit. The scary part is I had to break my formula up into three parts. Hehehe.
You need to get the updated version of Excel, it far exceeds 8,192
-
It's scary that you know that, Mike.
-Rimist (via Tapatalk)
-
At least I found something that I know and you don't.....made my day.
Everyone on this thread, their knowledge.....scares the bejeeves out me.
I'm still a student in all regards - pre K(indergarten) to be exact.
Let me know when I graduate to 1st grade.
MH
-
Might I recommend you modify the formula from
=A1+A2+A3+A4+A...
to
= sum(A1:A5000)
;D
Now that's funny, but at the same time I have seen that before! <fp.
-
I have extensive knowledge in Excel - on another note. If you need me to do any formula configurations, pivot tables, I may be able to assist. I can work up a quick spread sheet with fill in the blanks, pull down tabs, filters, etc.
That's about all I can offer to those less informative......can some teach me the ways of soldering?????? LOL
<la..
That would be a fair trade.....I think
-
Macros are only dangerous when you receive a spreadsheet that is infected and run the macro.
Writing macros for your own use is perfectly safe.
If you don't know about a spreadsheet that someone has sent to you, the safe thing is to disable the macros (unless you have disabled the prompt, excell will ask when you try to open the file), then open the file and look at what they have written.
RM
-
Macros are only dangerous when you receive a spreadsheet that is infected and run the macro.
Writing macros for your own use is perfectly safe.
If you don't know about a spreadsheet that someone has sent to you, the safe thing is to disable the macros (unless you have disabled the prompt, excell will ask when you try to open the file), then open the file and look at what they have written.
RM
and if there is anything you don't understand, don't run it!
-Rimist (via Tapatalk)
-
All the macros in the spreadsheets I have posted (and will continue to post) are completely developed by myself. Just in case any of yall use the sheets I post.
Kurt
-
All the macros in the spreadsheets I have posted (and will continue to post) are completely developed by myself. Just in case any of yall use the sheets I post.
Kurt
Haha...I wasn't worried about yours but I do know you can download them and set yourself up for failure if your not careful.
-
Wow scary. I think it's time to create your own visual basic module =D. Editing that formula could be a pain and validation can and may be off <md.. <res.
i'm curious to see this formula ;D
I probably shouldn't do this...but....here it is. Well 1/3 of it anyways.
It is basically nested if statements. In hind sight there was a couple of other options to do this.
-
I looked at your code a little and still wonder what it is you are trying to do.
Excel has som really neat features using named ranges and the "SUMIF" command that might be better for what you are trying to do...
Filters are also very powerful and there is a conditional sum that works with them as well.
RM
-
I looked at your code a little and still wonder what it is you are trying to do.
Excel has som really neat features using named ranges and the "SUMIF" command that might be better for what you are trying to do...
Filters are also very powerful and there is a conditional sum that works with them as well.
RM
That is part of the logic statement I made to choose shipping options for a COOP. If someone orders 1 kit that will fit in a medium box it checks to make sure that it can't fit in a small box then moves on to the next box size, etc. Then it looks at how many kits it can fit in the box, say 1-3 for a medium box. If someone orders 4 kits it checks to see if it can fit in the medium box based on the number of kits. Then it goes to the next larger size box...etc. It then returns a shipping cost based on the number of kits chosen. If the number of kits exceeds the maximum amount that can be held in the largest box it returns an error to let you know it needs to be looked at.
The reason it has basically the same nested loop repeated is when the COOP manager chooses which COOP they are going to do it associates the shipping parameters.
The reason this is only one third of the code is for US, CAN/MEX and International shipping.
I know it is ugly but I am trying to see the feasibility of how it will all work to see if it needs further development.
Rich
-
All I can say is I see enought excel formulas at work, this just re-established my headache ???
-
Maybe you should put in a table and then use a lookup table to the criteria of the price breaks you are after. Then infuse that query into your if, then nested function. It might help you out substantially. You would just need to correct the price break table when prices change. For instance IF(B2>50,Vlookup(table,column),""). I think a table would help your query greatly cut down the data inputs.
-
Maybe you should put in a table and then use a lookup table to the criteria of the price breaks you are after. Then infuse that query into your if, then nested function. It might help you out substantially. You would just need to correct the price break table when prices change. For instance IF(B2>50,Vlookup(table,column),""). I think a table would help your query greatly cut down the data inputs.
Yeah.....I actually did that already and had added an item to the list and didn't sort it so it didn't work. That is when I decided to use muscle instead of.... <fp.
Anyways, I finished removing all of the nested if statements out yesterday and I am using the lookup function now.
Rich
-
I see you have it working, congrats man!