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,

 

[FDibbins]

Nicely done. Suggested improvements...
=IF(E4="","",E4-D4+F3)

1. you don't need ABS
2. bring the +F3 inside the IF statement, otherwise you get errors when you copy down to empty rows

 

[WarfritLive]

Nicely done. Suggested improvements...
=IF(E4="","",E4-D4+F3)

1. you don't need ABS
2. bring the +F3 inside the IF statement, otherwise you get errors when you copy down to empty rows

Thank you for your insight.
I'd just like to say that ABS I think is required because of something he mentioned in the original post, about one of the columns possibility of being negative

 

[FDibbins]

OK yes I see that now - although if this is production, I don't see how you could produce negative anything lol

 

[Lestat]

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>

Thanks all! The above works fine except that I'm still getting #VALUE! errors all the way down the rows where nothing has been entered yet. I thought the IF (X="","") should take care of that, but it isn't for some reason.

To the person who recognized that it's possible to have negative production, I agree But I'm adding identical columns that have to do with dollars so thought I'd ask in one shot.

Btw- How do you guys insert those tables? It would be much more helpful for me in the future when I have a question.

Thanks again! If anyone knows how to correct my #VALUE! problem, I'd really appreciate it. Otherwise, the math is correct.

Edit: I think it's because it's adding ""+ the number above it, but still don't know how to fix it.

 

[FDibbins]

Instead of this sort of thing...
=if(e4="","",abs(e4)-d4)+f3
put the +f3 inside the )...
=if(e4="","",abs(e4)-d4+f3)

Also, see my signature for how to include tables

 

[Lestat]

Instead of this sort of thing...
=if(e4="","",abs(e4)-d4)+f3
put the +f3 inside the )...
=if(e4="","",abs(e4)-d4+f3)

Also, see my signature for how to include tables

Thank you FDibbins. Works like a charm! It's impressive how good you guys are. I'll never be a programmer, but it would be nice to simple stuff like this. I know most of the simpler functions, but I can never seem to format formulas correctly without getting an error. I'm going to read more so I know this stuff.

I do have one more thing and this spreadsheet will be complete. Not sure if I should start another thread or not...

I'd like to create a graph (I'm thinking bar, but maybe pie and maybe both), that shows the difference compared to what the target is supposed to be. This gets tricky because I cannot sum the difference, but have to use just the last row that has an entry. For instance, if F10 is -7, then the difference bar needs to pull just F7 (while the target bar needs to sum rows F4:F7. Then if the next day F11 is -3, then the difference graph needs just pull just F11, while the target bar sums F4:F11. I'm not even sure if there's a way to do this. Whether you can help with this or not, I really appreciate your time so far. Thanks! Lastly, I did look in your signature. Is it under BB codes? I couldn't find how to insert a table. Also, I cannot start a new paragraph in this post! When I do, my space bar isn't working! Sorry this is run on. Always computer probs... lol

 

[Lestat]

Target	Actual	Target	Actual	Diff	Diff
Hours	Hours	$	$	Hours	$
10	15	250	-1273	5	-1523
10	10	250	-1536	5	-3309
5	1	250	-2120	1	-5679
10	4	250	1063	-5	-4866
5	4	250	-219	-6	-5335
10	4	250	536	-12	-5049

<tbody>
</tbody>