I have a model that will need to distinguish actual input figures from estimated input figures. It has a many (hundreds) of input columns stacked on top of each other in different sections, so adding an extra column alongside each value to signify “e” for estimate is not practical. My idea right now is for the user to signify an estimate by adding “^e” to the number (ie, “$1,000^e”). This would be easy to implement but it will wreak havoc on my formulas because Excel will read “$1,000^e” as text and give me an error. The formulas are not complex and do not do much more than add, subtract, or divide cells. My current thinking is to just knuckle up and edit each formula so that it tells Excel to FIND() the “^” and then pull the values LEFT() of the hat. This isn’t an ideal solution because it is too tedious and has too much potential for error. Is there an easier way to do this? Perhaps some kind of macro that will tell Excel to always ignore “^e” whenever it uses a number in a formula?
Don’t understand what you’re trying to do, but if you want to add text to your number and want excel to treat it like a number, it’s pretty easy. control 1 for format cell click on custom and add at the end “e” For example, if your cell has 800, after you format it will read 800e, but will be treated as 800 so you can do your math.
Maybe i’m missing what you’re after, but value(left(A1,len(A1)-2)) would extract the 1000 from the cell that reads 1000^e, but I don’t know if you have a reference in place to let you know where to draw the data from (a vlookup refrence, etc).
I don’t completely understand as at some point you will have to change the formulae to deal differently with the actuals and estimates. You could create some new sheets that are copies of the inputs sheets. (I’m assuming you have been sensible enough to separate the inputs). Use these sheets to separate the info you need i.e. you can have one clumsy formula that works. e.g. Sheet 1 1e 34 2e Sheet 2 1 34 2 Sheet 3 e e Then change the forumlae such they are IF(sheet 3,then assume estimate,they are actuals) etc… Obviously if you are intending to only use one set of numbers at one time then that would be pretty straight forward and you wouldn’t need to change the formulae.
Thanks all for the input. Hobbes/Bean - Those ideas might work on a smaller project, but this spreadsheet has hundreds, if not a couple thousand, input cells and will be duplicated 50-100 times. I need something I can change on the template and then be good to go. AbbeFaria - Yea, that’s a good method. I like that you don’t use the FIND() function because the ^e will always be the last two characters of the cell. If the original formula, without controlling for estimates, is =a1-b1 then I’m between doing =value(left(A1,len(A1)-2)) - value(left(b1,len(b1)-2)) or the SUBSTITUTE() function, which I just learned about =substitute(a1,"^e","") - substitute(b1,"^e","")
Young_Prof Wrote: ------------------------------------------------------- > Thanks all for the input. > > Hobbes/Bean - Those ideas might work on a smaller > project, but this spreadsheet has hundreds, if not > a couple thousand, input cells and will be > duplicated 50-100 times. I need something I can > change on the template and then be good to go. > You should read the above posts a bit more carefully: - Hobbes is saying that you can change the formatting to show an e after the number. This would only be formating and thus would have no material impact on your actual numbers. So 1000e would still be 1000, but would look like 1000e. You would therefore not need any work around change to the forumlae. If you are worried about your users being able to do this you only need to apply a simply style (i.e. an Excel style) to this formating. You could throw in some colours too if you wished. - I was saying that if you need to ensure there is a material difference between the two things you are better off doing this on a separate sheet. I said this because of the numerous inputs and the ease with which one can copy a sheet and change everything. Extracting all character numbers equal to 0 Mod 4 in “dfjdskfjkejfkjekjkejf” is trivial. That is what AbbeFaria gave you. The point being that doing so in thousands of places in formulae would be clumsy, and hence I recommended you do it on a separate sheet where you can see the results.
Really? I actually started commenting on each of your bullets in detail but I quit because you are obviously confused about what I’m doing and I shouldn’t waste my time defending myself. But this bugs me so I kind of will. Instead of making the safe assumption that there may be complexities in this project that you don’t appreciate (e.g., having to develop program that will pull the all the sheets into a SAS database later on - which negates any benefit of the custom formatting and multiple sheets comment), you just assume that I’m not reading the posts carefully. I said “thanks all” because I appreciated yours and other comments. I then mentioned Abbe’s comment particularly because it best fit my needs, no need to take offense because I’m not using your idea.
Young_Prof Wrote: ------------------------------------------------------- > Really? I actually started commenting on each of > your bullets in detail but I quit because you are > obviously confused about what I’m doing and I > shouldn’t waste my time defending myself. > > But this bugs me so I kind of will. Instead of > making the safe assumption that there may be > complexities in this project that you don’t > appreciate (e.g., having to develop program that > will pull the all the sheets into a SAS database > later on - which negates any benefit of the custom > formatting and multiple sheets comment), you just > assume that I’m not reading the posts carefully. > > I said “thanks all” because I appreciated yours > and other comments. I then mentioned Abbe’s > comment particularly because it best fit my needs, > no need to take offense because I’m not using your > idea. I did not take any offence, - I was trying to help you out by offering further explanations. I particular dislike all the negative comments that are often found on this forum, and my intention was constructive even if it did not come across this way. Your additional comment does indeed explain why the other solutions did not help. The reason I assumed you were not reading closely enough was because instead of saying that in the first place you said the other ideas wouldn’t work because there was a lot of data. As that was precisely the reason I had suggested it I thus jumped to the conclusion that I had not explained it carefully enough / you had not read it fully. Combined with the relatively simple formula Abbe put forward, of which you were unaware, meant I thought I would try to clarify things. Anyway, my apologies.