I have a column of cells calculating Net soil cut/fill that displays the net value and if it's cut/fill:

Col A  Col B  Col C
Cut     Fill    =ABS(A1-B1)&IF(A1>B1," (Cut)", " (Fill)")
1,500  200    1,300 (Cut)

5/4/2012 11:53:26 AM

=TEXT(ABS(A1-B1),"_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)")&IF(A1>B1," (Cut)", " (Fill)")

5/4/2012 11:57:38 AM

woah

5/4/2012 12:02:19 PM

doesn't that turn the cell to text? need it to be a value for further calculations. guess I should have said that.

thanks for your help

[Edited on May 4, 2012 at 12:03 PM. Reason : .]

5/4/2012 12:03:15 PM

you're going to do a calculation on a cell with a value of "1,300 (Cut)"?
why don't you put =ABS(A1-B1) in column C and then do your concatenation with the cut/fill text in column D

5/4/2012 12:05:55 PM

that's how I would do it to make it simple, but not my sheet and not my rules, etc. I was hoping there was a way to do this in one cell and still have it calculate.

5/4/2012 12:09:19 PM

could you recalculate the value or convert to number before your next calc?

[Edited on May 4, 2012 at 12:33 PM. Reason : ...]

5/4/2012 12:30:33 PM

You got something weird going on with your excel file if it will not show commas. I can do what you want by formatting the cell, going to number and then checking the Use 1000 Separator (,). If you do that what happens? Nothing?

5/4/2012 2:12:03 PM

I've never had a custom cell format work the way I wanted it to.

5/4/2012 2:12:38 PM

i'm confused... can't you just format cell, go to number and check box "Use 1000 Seperator (,)"?

wait, i see the problem... yeah, that's probably not gonna happen

[Edited on May 4, 2012 at 3:23 PM. Reason : ]

5/4/2012 3:22:41 PM

It can happen. I just did something similar with a date instead of a number. When I get back to my laptop I'll show how to make it happen (later tonight)

5/4/2012 7:07:08 PM

Oh wow I totally did not read what you wrote. If you havent figured it out by now try this out....

=IF(C5>D5,ABS(C5-D5)&" CUT",ABS(C5-D5)&" FILL")

Did I redeem myself?

[Edited on May 7, 2012 at 7:36 AM. Reason : Oo]

5/7/2012 7:35:15 AM

When you concatenate the (Cut) or (Fill) to the end of the value, Excel converts the ABS(A1-B1) to text, so the entire formula result is now a string. Knowing that it will become a string, you need to add the commas in yourself in the formula using something like

=IF(ABS(A1-B1) >= 1000, FLOOR(ABS(A1-B1)/1000, 1)&","&MOD(ABS(A1-B1), 1000), ABS(A1-B1))&IF(A1>B1," (Cut)", " (Fill)")

That function isn't quite right, since it will fail if the difference is something like 1,050. It won't zero-pad the value after the comma, so it would end up returning "1,50 (Cut)". But if you can fix that so it inserts the zero(s) when needed, you'll be all set.

However, this still leaves the problem of calculating with the value later when the cell result is a string. If you only need to do this a few times, it's not too bad of a hassle to extract a number from this, but it's kind of an unwieldy expression to be throwing around if you need it in lots of places.

=VALUE(SUBSTITUTE(LEFT(C1, FIND(" ", C1)), ",", ""))

That expression uses LEFT to isolate the number before the (Cut) or (Fill) designation, strips the comma, then converts the result from text to number. So any time you need to calculate on that cell, you'll have to use that to extract the number.

Pain in the ass, but if those are the rules you're given to work with...

[Edited on May 7, 2012 at 3:42 PM. Reason : zero-pad issue]

5/7/2012 3:25:48 PM

This solves the zero-pad issue, assuming the values in Col A and Col B are integers. If you have any decimals, it'll be trickier to deal with rounding, etc.

IF(LEN(MOD(ABS(A1-B1), 1000))<3, RIGHT("00"&MOD(ABS(A1-B1), 1000), 3), MOD(ABS(A1-B1), 1000))

So then the full expression for Col C becomes

=IF(ABS(A1-B1) >= 1000, FLOOR(ABS(A1-B1)/1000, 1)&","&IF(LEN(MOD(ABS(A1-B1), 1000))<3, RIGHT("00"&MOD(ABS(A1-B1), 1000), 3), MOD(ABS(A1-B1), 1000)), ABS(A1-B1))&IF(A1>B1," (Cut)", " (Fill)")

5/7/2012 3:53:34 PM