number and formula in same cell 2

[georgyboy]

Hi,
Cant think of any words to describe this for the search engine.

How would you put a formula in a cell as well as having a number displaying at the same time which is part of the formula??

in cell q14 is the number 68$ (price of item) i need this price to increae by the number ordered when the quantity ordered is inputted (is that a word) into the worksheet

thanks

 

[eman6]

'Input' stays input in the past tense, just like 'put' and a number of other verbs in English
As regarding your question:
The total price = Unit price * Quantity anyway, so if you just have quantity set to 1 by default you get the total as 68$ as you had and if quantity is changed, the total changes too.
Or have I not understood your question correctly?

_______________________________________

Eman_2005
Technical Communicator

http://www.smartistek.com

 

[georgyboy]

in a way yes. Sorry but i am finding it hard to describe.
i want qty to be 0 at first and still have the total cell with 68 in it. Then when someone puts, say, qty of 5, the total box increase 68 *5. I cant seem to 68 stay visible in the total cell and also have the formula in it as well

confused?? i am :/

6 7 8 9 10 11 12 13 14
no quantity 68$
examples
6 7 8 9 10 11 12 13 14
1 1 1 204$

 

[eman6]

I understand what you say.
But to make it logical, maybe you better add another column.
However, (and before you say Grrrrrrr), you can do that by if, then and else. Click on the fx in the formula bar and use the IF function. make the value if these cells =0 equals 68$, annd in the else field put the calculation formula you have.

Let me know if that was OK.

_______________________________________

Eman_2005
Technical Communicator

http://www.smartistek.com

 

[DPlank]

In the cell where you would put 68$, try something like :

=if(sum(range of rows in here)=0, "68$", (sum(range of rows in here)*68)&"$")

Hope this helps
Dave

"Probably the only Test Analyst on Tek-Tips"

Take a look at Forum1393 & sign up if you'd like

 

[KenWright]

How about

=MAX(68,SUM(Range)*68)

and format the cell as custom to put the $ at the end

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[macropod]

Or even
=MAX(1,SUM(Range))*68&"$"


Cheers

 

[KenWright]

Depends on whether you need the data to be a number or text though. If further calcs are made using the data then better off leaving it numeric.

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[georgyboy]

thanks heaps everyone!!!!
stoked to have figured out with your help.
i ended up using dPlanks code (cheating yes, but i did learn from it!!)
looking forward to one day contributing to this forum

George

 

[AnotherHiggins]

georgyboy,

A word of caution about DPlank's solution:
Your answer is now TEXT! Try taking your solution and adding 1. It doesn't work. You'll run into the same problem with macropod's solution. (No offense, guys.)

Go with KenWright's solution - it returns an actual NUMBER that you can do further arithmetic with.

If you are unsure about the custom formatting he refers to, just select the cell with the formula, right click, select Format Cells. From the list on the left, select Custom. In the box at the top right, type in 0"$". That's it.

Now you have your dollar sign after the numbers like you want, and have greater flexibility for future development because the answer is a number.

Believe me, you'll be saving yourself headaches later if you keep your numbers as numbers now.

[tt]_____
[blue]-John[/blue]
[/tt][red]"If you're flammable and have legs, you are never blocking a fire exit."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[georgyboy]

anotherhiggins, thanks for the headsup. But i am confused on how you say tits text now and not a number.
i tried to do another calculation with the number the formula produced and everything seemed to work fine, i got the right answer, etc.

FYI i changed dplunks formula a little, i got rid of the dolalr sign at the end of it. i only needed the numbers, the very last cell will have a dollar sign in front it. make a difference to what you were saying??

thanks agian

 

[AnotherHiggins]

i got rid of the dolalr sign at the end of it .... make a difference to what you were saying??

Yes!

By using the ampersand ("[highlight]&[/highlight]") in the formula you would be concatenating the result of the formula with text - this converts this entire solution to a text string. By taking the "[highlight]&"$"[/highlight]" out of the formula you've already fixed the problem.

Sorry for the false alarm. I went down this road when I was new to Excel and just didn't want you to make the same mistake I did.

[tt]_____
[blue]-John[/blue]
[/tt][red]"If you're flammable and have legs, you are never blocking a fire exit."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[georgyboy]

thanks mate - stoked!