+ Reply to Thread
Results 1 to 2 of 2

How to return a Zero instead of a FALSE when referencing a cell with a formula in it?

  1. #1
    Registered User
    Join Date
    07-26-2014
    Location
    Nottingham, UK
    MS-Off Ver
    Microsoft Office Professional 2010
    Posts
    32

    How to return a Zero instead of a FALSE when referencing a cell with a formula in it?

    Hi,

    I'm having an issue getting my spreadsheet to do what I want it to do.
    For each month of the year I have 2 cells that I want to use to calculate a formula in a 3rd cell each month. I'll call them Cell1, Cell2 and Cell3 (Cell3 being the formula cell for the calculation).

    For each month of a year I want Cell3 to calculate figures from Cell2 based on what letter Cell1 has in it.

    Cell1 each month has either N, U, C or CU in it or it is "blank". I use the term blank with quotation marks because the cell isn't necessarily blank, it will contain a formula to equal Cell1 from the previous month. If the previous month's Cell1 is an empty cell then it will return a blank cell/zero.

    e.g:
    January's Cell1 is called A1, February's is called F1 and March's is called M1. February's cell will contain the formula =A1 and March's cell will contain the formula =F1.

    If January has an "N" in A1 then F1 and M1 will also have an N in it because the cells' formulas equal the previous month's. If January was an empty cell and had no letter in it, then F1 and M1 would return a blank cell but wouldn't actually be an empty cell because they contain a formula.

    For each month I want Cell3's formula to multiply Cell2 by 13.80 IF Cell1 has an N or U in it and multiply Cell2 by 10.80 IF Cell1 has a C or CU in it. If Cell1 has a "blank" cell then I just want Cell3 to return a zero.

    The formulas I currently have for Cell3 for January and February is:

    Jan: =IF(OR(D11={"N","U"}),K11*13.8,IF(OR(D11={"C","CU"}),K11*10.8,IF(D11="","")))
    Feb: =IF(OR(P11={"N","U"}),AH11*13.8,IF(OR(P11={"C","CU"}),AH11*10.8,IF(P11="","")))

    In January, Cell1 is an empty cell so the formula for Cell3 in January (the 1st one) works fine because it recognises that D11 is an empty cell.
    For Feb, Cell1 (P11) is "blank" because it equals Jan's D11 cell which is empty but contains the formula =D11 so Cell3 for Feb returns a "FALSE" value when I want it to return a zero/blank cell.

    I hope that all makes sense! How do I resolve this?

    Kind regards,
    Sally

  2. #2
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: How to return a Zero instead of a FALSE when referencing a cell with a formula in it?

    Click into your formula and put your cursor between the i and f of the first "if" and click the fx button next to the formula bar and this will bring up the function arguments box and it will show your logical test part of your formula, the part to return for true and the part to return for false. Make sure the false part is how you want it and then repeat this for every if word in your formula.
    Click the * Add Reputation button in the lower left hand corner of this post to say thanks.

    Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

+ 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. Replies: 2
    Last Post: 09-18-2014, 05:41 PM
  2. I need my formula to return a blank cell rather than FALSE
    By dnoteman in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-07-2014, 03:51 PM
  3. [SOLVED] Make a IF formula return a REAL blank cell if false
    By Stildawn in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-28-2012, 11:09 PM
  4. Return True/False if cell contains formula
    By rziegler in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-09-2009, 03:03 PM
  5. How do I create a formula to return a blank cell if false?
    By Vivecat in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-28-2007, 07:33 AM

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