This has to be so easy!

[Lestat]

Hi,

Sorry for this simplistic question, but I'm not that versed in Excel (obviously)...

I have a very simple IF formula in column G: =IF(E4>=0,E4-D4,E4+D4) (btw- I'm doing this to get around the fact that E4 can be negative number and I only want the difference between E4 and D4 (not a sum and D4 will always be a positive. So if I simply do =E4+D4 and D4 is 250 and E4 is -500, I get -750, instead of the difference, which is -$250. I know there is also a way to flip an integer into either positive or negative so I can simply use E4+D4 always, but I don't know how to do that either). So already, this formula is more complicated than it needs to be. But my real problem is...

If E4 is blank (or has no value), I want "" or the cell to simply be blank. I can't seem to do this without getting a too many arguments error.

Can someone give me the cleanest formula to achieve what I need? Really sorry for the simple question, but I'm really not experienced with Excel, although I'm trying to learn.

Thanks,

 

[Gerald Higgins]

Hi, I'm sure what you want is doable, but it would probably be helpful if you posted a small table showing SEVERAL DIFFERENT scenarios for what these values could be, and in each case, what you want the results to be.

IMPORTANT - show SEVERAL, DIFFERENT scenarios.

 

[63falcondude]

Check out the absolute "=ABS()" function.

If E4 is blank... =IF(E4="","",formula)

If you cannot figure it out from here, please post some sample data and the desired outcome of the formula for us to work with.

 

[MattH1]

Before I attempt to give you a formula for this, let me just try to clarify what you're asking for so I can accurately help you.
You're looking to find the difference of the ABSOLUTE VALUES of E4 and D4
Examples:
1. E4 = 10, D4 = 20. G4 will equal -10
2. E4 = -10, D4 = 20. G4 will equal -10
3. E4 = -10, D4 = -30. G4 will equal -30
4. E4 = BLANK, D4 = 20. G4 will be BLANK.

If this is correct, let me know and I'll arrange a formula for you.

E: This is what I was thinking:

=IF(ISBLANK($E4),"",ABS($E4)-$D4)

Let me know if this works for you.

 

[WarfritLive]

=if(e4="","",abs(e4-d4))

 

[Lestat]

Sorry I'm being confusing.

Let colmn A represent a date. Column B will represent a fixed number, which is the target for that date (say 10). Column C will represent the actual number achieved for that date (say 5). Column D will represent the difference between target and actual (in this case -5).

1. B = 10, C = 5. This means D = -5
2. B =10, C = 15. This means D = 0!

Because I want a running total of the difference. So -5 + 5 = 0.

If now on the 3rd and 4th days:

3. B = 5, C = 7. This means D = 2
4. B = 10 C = 7. This means D = -1

Does that make sense? Column B will already have a predefined value in it going down about 109 rows (dates). But column C will only have an entered value at the end of the day. And I'd like column D to be blank until column C has a value and then represent the overall sum of the difference, but not going past the rows of column B if column C doesn't have a value in it yet.

I'm on my phone. If it's still unclear I can post a table of what I mean.

Thanks.

 

[FDibbins]

Perhaps this?

	B​	C​	D​
1​	TGT	ACT	DIFF
2​	10​	5​	-5​
3​	10​	15​	0​
4​	5​	7​	2​
5​	10​	7​	-1​

D2=IF(B2="","",SUM($C$2:C2)-SUM($B$2:B2))
copied down

 

[WarfritLive]

=if(e4="","",abs(e4)-d4)+f3

Edit: Dang, beat me to it FDibbins
Mine is just the formula I posted earlier, plus it adds the cell above it to the count

 

[FDibbins]

=if(e4="","",abs(e4)-d4)+f3

And where is that supposed to go?

 

[WarfritLive]

Sorry, it's using the cell references from his earlier example. It will look like this:

A	B	C	D	E	F	Result below
2
3
4			10	5	=if(e4="","",abs(e4)-d4)+f3	-5
5			10	15	=if(e5="","",abs(e5)-d5)+f4	0
6			5	7	=if(e6="","",abs(e6)-d6)+f5	2
7			10	7	=if(e7="","",abs(e7)-d7)+f6	-1
8

<tbody>
</tbody>