Finding the Right Formula

[Guest]

Hi, im trying to set up a stock control prog in excel. Im having trouble in
making the answer to end as meters (as in length). i.e.

In Stock Amount Needed Amount to Order
20meters 50meters 30meters

I can get the answer without using the "word" meters. If any one can help or
give me the name of a Stock Control Programe i would be forever grateful. Im
trying it on excel for the reason that im quite used to excel and the amount
of stock i have to control, may not justify the expense of a programe.

Thanks
Greg

 

[Guest]

If your unit of length is always meters why not make your headings include
the word meters rather than include it in the numeric values. This makes use
of formulae much easier, particularly arithmetic operations.

In Stock Amount Needed Amount to Order
(meters) (Meters) Meters)
20 50 30

Or I have I missed (misunderstood) something?

 

[Beege]

peaspud,

Try Format/Cells/Custom

[<=1]####0.00" Meter";[>1]####0.00" Meters"

Beege

 

[Guest]

Will all of your data always use "meters"? If so, then you could use Cell
Formatting to achieve this.

FORMAT-->CELLS-->Number Tab

Enter the Custom Number format: 0 "meters"

Now, you just need to type the numbers into your cells and Excel will
automatically apply "meters" after each number. The value in the cell will
remain a number, so calculations may still be performed.

HTH,
Elkar

 

[Guest]

peaspud,

Try Format/Cells/Custom

[<=1]####0.00" Meter";[>1]####0.00" Meters"

Beege

Hi,
Thank you all for helping me out.

Beege your help is the one which seems to fit perfectly,bar one little hitch.

I was getting a negative answer when i had sulplus stock i.e.
Need Got Total
50 meters 65 meters -15 meters
Your formula seems to correct that. But is there anyway of getting rid of the
(>1) or the (<=1) that appears in the total box (example below)
Need Got Total
25 30 (>1)5.00meters

Can you use the formula with colours RED for a Negative BLUE for a Positive??
Or is that asking too much??
Thank you all again for your help
Greg

 

[Beege]

Peaspud,

Format/Cells/Custom

[Blue][>1]#0.0 " Meters";[Red][<0]#0.0 " Meters in Stock";[Blue]#0.0" Meter"

Be sure to use the square brackets for the colors and conditions.

Beege

peaspud,

Try Format/Cells/Custom

[<=1]####0.00" Meter";[>1]####0.00" Meters"

Beege

Hi,
Thank you all for helping me out.

Beege your help is the one which seems to fit perfectly,bar one little
hitch.

I was getting a negative answer when i had sulplus stock i.e.
Need Got Total
50 meters 65 meters -15 meters
Your formula seems to correct that. But is there anyway of getting rid of
the
(>1) or the (<=1) that appears in the total box (example below)
Need Got Total
25 30 (>1)5.00meters

Can you use the formula with colours RED for a Negative BLUE for a
Positive??
Or is that asking too much??
Thank you all again for your help
Greg