1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by cyrus123, Jan 21, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. cyrus123

    cyrus123 Thread Starter

    Joined:
    Jan 21, 2009
    Messages:
    5
    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
      File size:
      28.5 KB
      Views:
      284
  2. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,175
    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.
     
  3. cyrus123

    cyrus123 Thread Starter

    Joined:
    Jan 21, 2009
    Messages:
    5
    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 :)
     
  4. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,175
    What are you going to be using this for? Are you selling in-game materials for real world cash?
     
  5. cyrus123

    cyrus123 Thread Starter

    Joined:
    Jan 21, 2009
    Messages:
    5
    Hi,
    Sorry I didn't get your question.
     
  6. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,175
    You are selling stuff for a game like WOW or SWG right? What exactly are you selling?
     
  7. cyrus123

    cyrus123 Thread Starter

    Joined:
    Jan 21, 2009
    Messages:
    5
    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
     
  8. Albantar

    Albantar

    Joined:
    Dec 9, 2008
    Messages:
    95
    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:

  9. cyrus123

    cyrus123 Thread Starter

    Joined:
    Jan 21, 2009
    Messages:
    5
    Hi,
    Haha I was complicating the formula and confusing myself.
    Thanks a alot Albantar :)
     
  10. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/792784