[cyrus123]

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

[jimr381]

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]

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]

What are you going to be using this for? Are you selling in-game materials for real world cash?

[cyrus123]

Hi,
Sorry I didn't get your question.

[jimr381]

You are selling stuff for a game like WOW or SWG right? What exactly are you selling?

[cyrus123]

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]

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.

[cyrus123]

Hi,
Haha I was complicating the formula and confusing myself.
Thanks a alot Albantar :-)