IF formula not accepting 1?

Hello,

I posted this question yesterday:

I have a formula =E4*1.05/((D4*40*5.1))

The problem is, if there's nothing in the D cell then it errors out. However, I don't want to set it to a zero or anything....I'd actually like to remove that part of the equation if there's nothing in the D cell. For example, there's an error now because there's nothing in D4...but if you alter the formula to =E4*1.05/((40*5.1)) then it provides what I believe is correct. Does this make sense? Is there any way to say if there's an error ignore D4, or else use the new forumula?

Someone supplied what looked like a good solution: =IF(D4="",E4*1.05/(40*5.1),E4*1.05/(D4*40*5.1))

The problem is, whether I enter a 1 in D4 or enter nothing, the cell with the formula remains the same. It only changes once I enter a 2, 3 etc. That shouldn't be, because even 1 (which would be 204), would have an impact, right? Basically, if I use a one and E4 only has 60 the cell with teh formula stays the same....which isn't right.

Does this make sense? Can anyone help?

Hi, try:
=E4*1.05/if(D4="",1,D4*40*5.1)

Hi ragulduy,

I tried it...but when E4 is empty it shows 6056% and when I input a 1 it drops to 30%. That doesn't seem right either.

@OP,

In that case, the original formula is correct:
E4*1.05/(40*5.1)
is the same as
E4*1.05/(D4*40*5.1)
When D4 is = 1, so if you enter 0 and 1 you will get the same answer, I thought by "remove that part of the equation" you meant that you would want E4*1.05 if D4 is 0. Maybe you need to clarify with an example value in E4 what the correct values with D4 = 0, 1, 2 etc should be.

Good point....I'll try to clarify.

The original formula was =E4*1.05/((D4*40*5.1))

If there is nothing in D4, there is an error. However, when nothing is in D4, I'd like for it to do the following: =E4*1.05/((40*5.1)) the reason being that there should still be a percentage here if there are no employees in D4.

However, when using =IF(D4="",E4*1.05/(40*5.1),E4*1.05/(D4*40*5.1))

If there is nothing in D4 it's showing 30%, which seems right. But when I place a 1 in D4 it remains 30% (which isn't right - why would it remain teh same, it should be reverting back to (D4*40*5.1))? When I put a 2 it drops to 15%.

So basically, it seems to give me the same result whether I input 0 or 1 in D4 - and only changes when I put in a 2, 3 etc. (anything higher than a 1).

Hi

40*5.1 = 204
D1*40*5.1 = 1*40*5.1 = 204
So the formula is giving you the results you shoudl be expecting.