Dozens in Excel...

[Lazarus]

i thot mine worked well too

 

[Scooba da Bass]

I feel like a bit of a twat but apologies to Scooba, yours worked fine. For some reason Excel was putting the results in a white font on a white background so it looked like there was nothing there.

Hehehe. I was going to race Jup to do the whole solution but when my code didn't seem to work on that bit I gave up.

 

[Clown]

EXCEL RACE!!

Gr8 m8

 

[Scooba da Bass]

Go do some work or I'll find your bosses' phone number and tell him you're slacking off.

 

[Clown]

C'est le time de lunch.

G-g-g-g-g-g-g-rrrr8

 

[caLLous]

Wow, seeing your avatars together is like needles in my eyes!

Anyway, there's another Excel problem(!)

Sales are input in another column on sessional control form and used to calculate a variance (sales - consumption = variance). Sometimes the variance will produce a negative number (a deficit) and the formula really doesn't like this idea...

After creating a big forehead shaped dent in my keyboard I bugged some clever people on IRC and we got to working on this:

=IF(B1>=0,TEXT(ROUNDDOWN(B1/12,0),"0")&"."&TEXT(B1-ROUNDDOWN(B1/12,0)*12,"0"),"-"&TEXT(ROUNDDOWN(-B1/12,0),"0")&"."&TEXT(-B1-ROUNDDOWN(-B1/12,0)*12,"0"))

Where B1 is where the number is. The above converts the variance number into dozens...

What's the dozens to decimal equivalent of this please? Sorry it keeps getting more complicated. ;(

(And Jup, with the above I changed the "00" to "0" to see if it worked ok without the leading 0, and it did... was it the other formula that needed it? Because I think I've confused myself a lil bit.

 

[Jupitus]

CaLLous... I'm sure it can be fixed NP, but not when Ive had 6 cans of beer.... will work it out for you tomorrow

 

[Jupitus]

Add to what I put before and hide non relevant columns.

I2: input your sales in the dozens format
J2: =(INT(I2)*12+MOD(I2,1)*100) this is decimal sales number
K2: =J2-H2 this calcs the variance using the decimal numbers
L2: =IF(K2>=0,TEXT(INT(K2/12),"0")&"."&TEXT(MOD(K2,12),"00"),"-"&TEXT(INT(ABS(K2)/12),"0")&"."&TEXT(MOD(ABS(K2),12),"00")) this gives the result and corrects if negative in the required display format.

All you need to do is take the ABS(Number) to convert to positive and in cases where variance is negative you do the display on this and insert a '-' char in front.

Oh, and it may appear to work fine with "0" rather than "00" in the formula but there are cases where it won't CaLLous, so play safe please

 

[caLLous]

You make me feel all warm and fuzzy inside.

Thanks a lot Jup, you've made my job that little bit less tedious.