Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Excel 2007- Two formulas in one cell (Please Help)


(!)

cyrus123's Avatar
cyrus123 cyrus123 is offline
Junior Member with 5 posts.
THREAD STARTER
 
Join Date: Jan 2009
Experience: Intermediate
21-Jan-2009, 09:23 AM #1
Solved: Excel 2007- Two formulas in one cell (Please Help)
Hi...

I am using excel to create a POS software. I am facing trouble in one cell where I want it to use either one formula or another.

I have attached the file.
What I want:
In the form sheet, I have an invoice layout. The cell 'Line Total', I want it to calculate the product of "price/unit * quantity" OR "price/ctn. * ctn."
(CTN. is cartons or boxes). The current formula I am using is showing a '00' in the line total. The idea is that I want to either enter the number of CTNs. under the CTN field and get the line total OR enter the quantity under the quantity field and get the line total. So it is deriving the line total either based on the cartons or the (single) quantity.

Can anyone find a solution for this? Thanks
Attached Files
File Type: xls Test.xls (28.5 KB, 258 views)
jimr381's Avatar
Computer Specs
Member with 4,175 posts.
 
Join Date: Jul 2007
Location: Vienna, VA
Experience: Computer Illiterate
21-Jan-2009, 10:08 AM #2
Welcome to the forum. All you really need to do is have the formula add both of the results up. You really do not need an if function because they are going to order either or and something times nothing is always equal to nothing. So if they order 0 ctns then the total of that formula will be zero.
cyrus123's Avatar
cyrus123 cyrus123 is offline
Junior Member with 5 posts.
THREAD STARTER
 
Join Date: Jan 2009
Experience: Intermediate
21-Jan-2009, 10:27 AM #3
Hi again,
Thanks for the reply. But I don't want excel to add both of the results up. I want it to calculate the result based on whether I entered quantity or if I entered ctns. So if i enter ctns, it should use the formula "price/ctn. * ctns." but if i entered quantity and did not use ctns, then the formula should use "price/unit * quantity".
If anyone can answer it, can you please reattach the excel file with the answer. Appreciate your help
jimr381's Avatar
Computer Specs
Member with 4,175 posts.
 
Join Date: Jul 2007
Location: Vienna, VA
Experience: Computer Illiterate
21-Jan-2009, 10:32 AM #4
What are you going to be using this for? Are you selling in-game materials for real world cash?
cyrus123's Avatar
cyrus123 cyrus123 is offline
Junior Member with 5 posts.
THREAD STARTER
 
Join Date: Jan 2009
Experience: Intermediate
21-Jan-2009, 10:36 AM #5
Hi,
Sorry I didn't get your question.
jimr381's Avatar
Computer Specs
Member with 4,175 posts.
 
Join Date: Jul 2007
Location: Vienna, VA
Experience: Computer Illiterate
21-Jan-2009, 10:38 AM #6
You are selling stuff for a game like WOW or SWG right? What exactly are you selling?
cyrus123's Avatar
cyrus123 cyrus123 is offline
Junior Member with 5 posts.
THREAD STARTER
 
Join Date: Jan 2009
Experience: Intermediate
21-Jan-2009, 10:42 AM #7
No. I want to create a point-of-sale for my brother's business. He sells chinaware. Currently their invoice system is, how to put this nicely, very basic :P
Albantar's Avatar
Albantar Albantar is offline Albantar has a Profile Picture
Computer Specs
Member with 95 posts.
 
Join Date: Dec 2008
Location: Netherlands
Experience: Advanced
21-Jan-2009, 11:25 AM #8
Hi Cyrus and welcome.

If I understand correctly, on one line you will have either a quantity or a number of cartons but never both.

If you enter a quantity, you will leave CTN empty for that line, which means that its value will be "0". And zero times the carton price is always zero.

So your line total formula is simply:

=C14*G14+D14*H14

The formula you use in the B, C and D columns poses some problem though which causes #VALUE! to appear if no product is selected. There were also some dollars missing causing a copy error...

See attached file.
Attached Files
File Type: xls Test Albantar.xls (35.0 KB, 219 views)
cyrus123's Avatar
cyrus123 cyrus123 is offline
Junior Member with 5 posts.
THREAD STARTER
 
Join Date: Jan 2009
Experience: Intermediate
21-Jan-2009, 11:42 AM #9
Hi,
Haha I was complicating the formula and confusing myself.
Thanks a alot Albantar :-)
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


Tags
business application, excel 2007, two formulas in one cell

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑