+ Reply to Thread
Results 1 to 7 of 7

only show data if other column has a figure, otherwise show zero.

  1. #1
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    171

    only show data if other column has a figure, otherwise show zero.

    Hello!

    I only want the over/under column F, to show a figure result, if either column C (sales gst) or E (disbursements) have data inputted. Otherwise I want it to read $0.00

    attached file here..

    targets_over.under.xlsx

    kind regards..

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: only show data if other column has a figure, otherwise show zero.

    Here!

    In F2

    =IF(MAX(C2,E2)>0,SUM((D2-E2)-B2),0) and drag down
    Cheers!
    Deep Dave

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: only show data if other column has a figure, otherwise show zero.

    A few tips before I help with your question.

    You dont need to use SUM() unless you are adding up a range of values
    =SUM(C2*0.9) cab just be =C2*0.9
    =SUM((D2-E2)-B2) can just be =D2-E2-B2

    OK...
    you stated that if EITHER C or E have data (in other words >0?), so try this...
    =IF(OR(C2>0,E2>0),0,D2-E2-B2)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    171

    Re: only show data if other column has a figure, otherwise show zero.

    Thankyou awesome this works perfectly !

  5. #5
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    171

    Re: only show data if other column has a figure, otherwise show zero.

    FDibbins, thanks for the support and good advice,
    just (for others learning from this resource) the formula is right in calculation but needs true false values swapped, so: =IF(OR(C2>0,E2>0),D2-E2-B2,0)

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: only show data if other column has a figure, otherwise show zero.

    happy to help, I kinda figured I may have them the wrong way round, but once you have the basis, the rest is easier, so thanks for the update

    Also, thanks for the rep, always appreciated

  7. #7
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: only show data if other column has a figure, otherwise show zero.

    Glad it helped!

+ 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] One TextBox Show Column and the other show Cell address Solved by : ragulduy
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-26-2014, 05:17 AM
  2. Need to show latest figure input in a column
    By HelpNe in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-11-2013, 11:32 AM
  3. Replies: 5
    Last Post: 11-09-2009, 06:45 PM
  4. formula at the top of the sheet to show the latest figure in column
    By karloss in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-06-2009, 12:30 PM
  5. Show Data Lables show Amount & Persentage at the same time
    By ComcoDG in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-29-2008, 04:54 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