Need a MS Excel expert

[wutangnomo]

Is there any way I can create a custom formula?

I'm trying to create a formula or custom function wherein I type a value in a cell and it automatically puts "$" in front of and "/dozen" after.

For example,

Lets say I type "20" into a cell it would automatically show as: "$20/dozen"

Any help would be appreciated. Thanks!

 

[powerslave]

The dollar sign is easy. Just highlight every cell (click the box in the upper left corner of the workseet) in a worksheet and format cells to currency. Not sure about the "dozen".

 

[wutangnomo]

Thanks for the reply.

I should be a little more clear. I don't want to input just currency. I'm wondering if I can put anything in front and anything after (basically customize the cell to exactly my requirements). It's not necessarily just currency.

 

[bran987]

if it is Microsoft XP, go to Help and look up the function "concatenate"
you will have to play with it a little while to figure it out

 

[hamstershaver]

if it is Microsoft XP, go to Help and look up the function "concatenate"
you will have to play with it a little while to figure it out

good post

 

[Dial_tone]

Concatenate is what you want:

one column with just $ in it
one column with your number in it
one column with /dozen in it.

Concatenate those 3 into a 4th column and there you are.

 

[wutangnomo]

I've been playing around with the concatenate function. It is sort of what I want, but not quite. The problem with the concatenate function is that the values must already be entered for me to use it.

For instance,

I want the "$" first and "/dozen" last. The value inbetween I don't know what they are yet and will enter it as I go.

Is there a way to get around this?

 

[bran987]

you're going to have to have 2 cells to accomplish this

like for instance you can input your 'unknown' number into A1

then in A2, put the concatenate function in there with "A1" sandwiched in there

pull the formula all the way down for however many rows you need, then if necessary at the end hide Column A.

 

[hamstershaver]

agreed

 

[wutangnomo]

Ok I got it, but it is not what I want. Darn. It is close, but not efficient.

In effect I actually need 4 cells in order to accomplish this. I also cannot display my "$whatevervalue/dozen" in that very same cell I type the value in.

For instance,

If I type in "200" into cell C3, I cannot have it display "$200/dozen" in cell C3. It's not possible.

 

[bran987]

Ok I got it, but it is not what I want. Darn. It is close, but not efficient.

In effect I actually need 4 cells in order to accomplish this. I also cannot display my "$whatevervalue/dozen" in that very same cell I type the value in.

For instance,

If I type in "200" into cell C3, I cannot have it display "$200/dozen" in cell C3. It's not possible.

no, that is not possible in excel

but you can do it in 2 cells, you don't need 4

 

[wutangnomo]

Oh okay you're right. All I need are two cells.

Thanks anyway guys.

 

[bran987]

Oh okay you're right. All I need are two cells.

Thanks anyway guys.

lol I feel like I failed u

 

[hamstershaver]

lol I feel like I failed u

nah

 

[wutangnomo]

lol I feel like I failed u

lol

It's okay Bran. I still love you.

 

[Dial_tone]

I dunno what kinda crack you're smoking but according to your directions my suggestion works perfectly.

Cell A1 has "$"
Cell B1 has "20" or whatever number you enter
Cell C1 has '/dozen - you need that single quote so the / doesn't get interpreted
Cell D1 has the formula =CONCATENATE(A3,B3,C3)

Cell D1 displays $20/dozen just like you wanted.

 

[Dial_tone]

You also could have used the formula =CONCATENATE("$",B2,"/dozen") and leave out the A and C columns altogether

 

[bran987]

You also could have used the formula =CONCATENATE("$",B2,"/dozen") and leave out the A and C columns altogether

no you are right, but you still need 2 cells, but he wanted to use 1 for some reason, which is impossible

 

[wutangnomo]

I dunno what kinda crack you're smoking but according to your directions my suggestion works perfectly.

Cell A1 has "$"
Cell B1 has "20" or whatever number you enter
Cell C1 has '/dozen - you need that single quote so the / doesn't get interpreted
Cell D1 has the formula =CONCATENATE(A3,B3,C3)

Cell D1 displays $20/dozen just like you wanted.

lol I'm not on any crack, maybe that's the problem? lol

No you're right DT, what I was trying to get across didn't come out clearly. In that situation you are correct, but I'm not looking to string together multiple cells. I'm simply looking for the corresponding units to appear after I enter in my value in that same cell (it's for the look, but also ease of reading).

Basically all I want is this.

I have a spreadsheet and various cells throughout will represent something different. The values are always changing.

For example, in cell D8 it might be "$whatever/hours", in cell H14 it might be "$whatever/pigs", in cell R25 it might be "$whatever/sessions", in T39 it might be "$whatever/inch" etc etc etc. I just want to be able to plug in a value in a cell and have the corresponding units appear before and after it, but note that the value changes so I will be constantly updating and replacing old values with new ones.