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

✔ 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

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

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

:)

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

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

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!

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.

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 :)

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 :)

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.

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 :)

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.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History