formula works until I reopen spread sheet then "=#value!"


I have a formula that works until I save and close the spread sheet.  When I reopen the spread sheet the cell shows the last calculated value but "=#value!" is what is in the cell where the formula was. I saved the formula to another cell without the "=" sign.  If I put the equal sign in front of the formula it works without me changing anything else.  Why?



That would depend upon the formula and the values of other cells it references and their formats. The best way to provide context is to upload a sanitised copy of the worksheet and, if the formula also accesses other worksheets, the entire workbook.
Variatus (rep: 823) Oct 8, '17 at 11:27 pm
My guess is that it has to do with the data sources. Definitely need at least the formula in question or the workbook to better understandt he issue.
don (rep: 1247) Oct 9, '17 at 4:42 am
here's the formula:  =IF(LEN($B$1)<21,IF(AND(LEN($B$1)<11,$B$2=3)=TRUE,$F$4,IF(AND(LEN($B$1)<11,$B$2=4)=TRUE,$G$4,IF(AND(LEN($B$1)<11,$B$2=5)=TRUE,$H$4,IF(AND(LEN($B$1)<11,$B$2=6)=TRUE,$I$4,IF(AND(LEN($B$1)<21,$B$2=3)=TRUE,$F$5,IF(AND(LEN($B$1)<21,$B$2=4)=TRUE,$G$5,IF(AND(LEN($B$1)<21,$B$2=5)=TRUE,$H$5,IF(AND(LEN($B$1)<21,$B$2=6)=TRUE,$I$5,"check size")))))))),IF($B$2=3,((LEN($B$1)-20)*$F$6)+$F$5,IF($B$2=4,(LEN($B$1)-20)*$G$6+$G$5,IF($B$2=5,(LEN($B$1)-20)*$H$6+$H$5,IF($B$2=6,(LEN($B$1)-20)*$I$6+$I$5,"check letter size"))))).  It's for calculating the price of a banner that can have 4 different size letters.  Price changes after 10 letters.  After 20 letters each additional letter costs more.
baddog1016 (rep: 6) Oct 9, '17 at 7:00 am
You need to update your question with this information and not add it in the discussion. Same goes if you upload a sample file.
don (rep: 1247) Oct 9, '17 at 4:07 pm
Add to Discussion


Selected Answer

I think you can replace your formula with this one, if you care to make a few preparations.

=INDEX(Prices,IF(Letters>10,2,1),Size) + (MAX(0, Letters -20) * INDEX(Prices, 3, Size))
  1. Create a named range by the name of "Letters", perhaps in C1 but it could be anywhere. Enter this formula in it. =LEN(SUBSTITUTE(B1," ",""))
    This is because I think you don't charge for spaces. If you do, it would be just =LEN(B1)
  2. Add Data Validation to your cell B2. Allow only values between 3 and 6. This is so that you don't have to worry about a wrong size being specified.
  3. Create a named range by the name of "Size", perhaps in C2 but it could be anywhere. Enter this formula in that cell. =B2-2. This converts your 4 sizes into the numbers 1 to 4.
  4. Create another named range by the name of "Prices". Make it comprise the range F4:I6.  Note that the 4 columns of this range are numbered 1 to 4 and the 3 rows are 1 to 3. So, you use the INDEX function to pick any of the cells in the Prices range, for example, INDEX(1, 2) would be the price for up to 10 letters in size 4.
  5. Rows 1 and 2 contain the prices for up to 10 and up to 20 letters.  "IF(Letters>10,2,1)" will return a 1 for any number up to 10 and a 2 for any number bigger than that.
    Accordingly, "INDEX(Prices, IF(Letters>10,2,1),Size)" picks the basic price from your pricelist "Prices". 
  6. To this you wish to add any surcharge for extra letters. That is a simple multiplication of the chargeable number of letters by the price in row 3 of your pricelist. "MAX(0, Letters -20)" selects the larger of zero and Letters-20, and "INDEX(Prices, 3, Size)" picks the price per letter from the third row of Prices, depending upon size.


Thank you, that's awesome!  There are a couple of functions I've never used so this will be a great experence.  I will let you know how I make out.
    My formula wasn't going to work at my work anyway because it's too nested and we are on Excel 2007.  I figured I was going to have to use a couple of formulas and name them. 
baddog1016 (rep: 6) Oct 9, '17 at 6:09 pm
Absolutely brilliant!  Thinking enough to subtract what needs to be subtracted to use the index function makes a lot of other things I want to do easier!  I've never used (or really new about) the validation function because excpet for simple spreadsheets I've always only done them for myself.  Thanks again!
baddog1016 (rep: 6) Oct 9, '17 at 7:01 pm
Thank you for the accolade. I'm glad I could help out. Have a great day!
Variatus (rep: 823) Oct 9, '17 at 7:38 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login