Live Chat & Podcast at 1:00PM Eastern on Sunday!

 Search Search for: Business ApplicationsAll Forums

Junior Member with 5 posts.

Join Date: Jan 2009
Experience: Intermediate
21-Jan-2009, 08:23 AM #1
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
 Test.xls (28.5 KB, 223 views)
Senior Member with 4,173 posts.

Join Date: Jul 2007
Location: Vienna, VA
Experience: Computer Illiterate
21-Jan-2009, 09: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.
__________________
"Take what you learn and make a difference with it."
___________________________________________
"Oh, goody!"
Junior Member with 5 posts.

Join Date: Jan 2009
Experience: Intermediate
21-Jan-2009, 09: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".
Senior Member with 4,173 posts.

Join Date: Jul 2007
Location: Vienna, VA
Experience: Computer Illiterate
21-Jan-2009, 09:32 AM #4
What are you going to be using this for? Are you selling in-game materials for real world cash?
Junior Member with 5 posts.

Join Date: Jan 2009
Experience: Intermediate
21-Jan-2009, 09:36 AM #5
Hi,
Sorry I didn't get your question.
Senior Member with 4,173 posts.

Join Date: Jul 2007
Location: Vienna, VA
Experience: Computer Illiterate
21-Jan-2009, 09:38 AM #6
You are selling stuff for a game like WOW or SWG right? What exactly are you selling?
Junior Member with 5 posts.

Join Date: Jan 2009
Experience: Intermediate
21-Jan-2009, 09: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
Member with 95 posts.

Join Date: Dec 2008
Location: Netherlands
21-Jan-2009, 10: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
 Test Albantar.xls (35.0 KB, 176 views)
Junior Member with 5 posts.

Join Date: Jan 2009
Experience: Intermediate
21-Jan-2009, 10:42 AM #9
Hi,
Haha I was complicating the formula and confusing myself.
Thanks a alot Albantar :-)
 techguy.org/792784
As Seen On

WELCOME TO TECH SUPPORT GUY!

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

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)