Solved Formula shows minimum results when excel template is blank

June 11, 2013 at 02:06:38
Specs: Windows XP
I have the correct formula to calculate how much freight is to be applied to sales, yet the cell will still display the minimum cost of freight when the invoice template is empty (blank).
When data is entered into invoice, it calculates perfectly. Need to overcome this excel glitch so I can submit my assignment. I am over it, and desperately seek assistance.
Working with Windows 8 on other laptop.
Regards
Helen

See More: Formula shows minimum results when excel template is blank

Report •

✔ Best Answer
June 11, 2013 at 05:46:33
See how this formula works:


=IF(F39<=0,"",IF(F39>=1000,35,IF(F39<=500,15,25)))

It's a bit shorter, and covers the condition if F39 is Blank or Zero,
two different conditions.

MIKE

http://www.skeptic.com/



#1
June 11, 2013 at 03:16:50
My crystal ball is out for repair this week, :-)
so without seeing what your formula looks like,
I would guess, that you have something hard coded into the formula,
however the calculation is being done, or however you have the
formula configured, it defaults to the minimum when the target cell is blank.
But without seeing the formula, it's tough to know for sure.

Also, Excel questions are best asked in the Office Software forum.

MIKE

http://www.skeptic.com/


Report •

#2
June 11, 2013 at 03:48:33
Hi Mike,

Thank you for your input. I am using a nested IF formula as it is in a template, I am trying to hide it so that the cell F39 remains blank until sales are added.


=IF(0<>"",IF(F39<=500,15,IF(F39<1000,25,IF(F39>=1000,35))),"")

Any further thoughts?

With thanks
Helen
:)


Report •

#3
June 11, 2013 at 05:34:52
In your formula, the very first IF Condition

=IF(0<>""

What condition are you testing?
Zero is not a cell reference.

Shouldn't it be:

=IF(F39<>""

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
June 11, 2013 at 05:46:33
✔ Best Answer
See how this formula works:


=IF(F39<=0,"",IF(F39>=1000,35,IF(F39<=500,15,25)))

It's a bit shorter, and covers the condition if F39 is Blank or Zero,
two different conditions.

MIKE

http://www.skeptic.com/


Report •

#5
June 11, 2013 at 13:29:18
Thank you Mike for your input, though unfortunately I tried this but it made no difference. It is so annoying. I am so over my template project! LOL!

Report •

#6
June 11, 2013 at 18:28:13
Using Mike's formula, I get the following results.

Are they not what you are looking for?

=IF(F39<=0,"",IF(F39>=1000,35,IF(F39<=500,15,25)))


          F           G
39      1001          35
40      1000          35
41       999          25
42           		  <--- Empty cell for Column F empty
43       501          25
44       500          15
45       499          15
46         0              <--- Empty cell for Column F = 0

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
June 12, 2013 at 01:47:04
Hello Derby Dad,
love your work! I applied your formula and yes it worked!!!
my assignment is finally finished and has been submitted!
Thank you very much for you input.... very much appreciated :)

Report •

#8
June 12, 2013 at 01:49:04
Thank you too Mike! I really appreciate your feedback....
Actually, I think looking back the formula was the same... yes it worked!!
I am happy.... Thanks again :)

Report •

#9
June 12, 2013 at 04:13:50
It was all Mike's work. I simply tested his formula via copy paste.

Obviously I can't explain why it didn't work for you the first time, but full credit goes to Mike.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
June 12, 2013 at 04:44:10
Thank you again Mike for taking the time out to answer my query, and thanks to you Derby Dad for backing up Mikes' theory..
I finally handed in this piece of assessment this afternoon; it was such a load off!!
again, my humble thanks to you both :)

Report •

#11
June 12, 2013 at 09:40:27
The next question is:

Do you understand why the formula works? We don't usually answer homework question directly in this forum, we would rather lead you to the answer by offering suggestions.

If you handed in the assignment without understanding the formula, you haven't learned anything.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question