+ Reply to Thread
Results 1 to 5 of 5

Multiple formulas for one cell

  1. #1
    Registered User
    Join Date
    09-15-2017
    Location
    Phoenix
    MS-Off Ver
    2013
    Posts
    2

    Question Multiple formulas for one cell

    Trying to set up a cell to perform multiple formulas. Problem I'm running into is getting the cell to run both formulas or one or the other depending on the content of the cell.
    Got the formulas down (I think).
    Basically I want to have the number 20 assigned to cell C4 if cell C3 is greater than 100
    Used this formula in C4:
    =IF(C3<100,"20")
    All good to this point.

    The next part is ONLY if C3 is NOT greater than 100. So if C3 is 99.99 or below-I want to multiply C3 by D3
    =C3*D3

    Separately both formulas work fine. It's when I try to put them both in the same cell that things get screwy.
    So I want a 20 assigned to C4 if C3 is 100 or greater, and stop running formulas if true. Only if C3 is less than 100 do I want it to run the multiplication formula.
    What am I missing?

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Multiple formulas for one cell

    =if(c3 < 100, 20, c3*d3)
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,814

    Re: Multiple formulas for one cell

    You say that you want C4 to be 20 if C3>100, but your formula reflects the opposite logic. Perhaps that is the source of confusion? You are erroneously trying to both logic as If C3<100 then do 2 different things?

    Reading the text and ignoring the formula, it sounds like you want a simple choice between two options. Something like =IF(C3>=100,20,C3*D3) (note that I did not put quotes around the 20, so that the formula will return the number 20 and not the text string "20". If you really want it to return the number 20 as text, include quotes around the 20 like you did before).

    Did I interpret what you wanted correctly, or did I misunderstand something?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    09-15-2017
    Location
    Phoenix
    MS-Off Ver
    2013
    Posts
    2

    Re: Multiple formulas for one cell

    Your explanation is exactly what I'm looking for. Sorry I transposed the < > in the formula. I put the formula in exactly as you typed it...It doesn't seem to be recognizing the first part. My example--
    Value of c3 is 102.04
    so using the formula you provided c4 should be 20 with nothing further.
    It's still multiplying c3 and d3(Value of d3 is 0.20) giving me 20.41 in c3

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,814

    Re: Multiple formulas for one cell

    Assuming you are referring to mine where it is testing C3>=100, I cannot replicate your described behavior. Can you upload a spreadsheet where 102.04>=100 returns FALSE?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Multiple formulas in one cell
    By jayjaytesoro in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-17-2017, 03:55 AM
  2. Replies: 4
    Last Post: 10-19-2016, 02:45 PM
  3. [SOLVED] Multiple formulas in one cell
    By LocalAl in forum Excel General
    Replies: 8
    Last Post: 08-17-2015, 01:25 PM
  4. [SOLVED] multiple if formulas in one cell
    By kingsnipes in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-15-2013, 01:03 AM
  5. [SOLVED] multiple formulas needed in one cell to create a value in a new cell
    By jamietofs in forum Excel General
    Replies: 13
    Last Post: 11-18-2012, 06:40 PM
  6. Multiple formulas in selected cell without damaging previous formulas.
    By excel5111987 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 02-05-2011, 06:15 AM
  7. Replies: 2
    Last Post: 03-29-2010, 07:07 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1