+ Reply to Thread
Results 1 to 13 of 13

If there is nothing review the formula for 0 value

  1. #1
    Forum Contributor
    Join Date
    08-07-2018
    Location
    india
    MS-Off Ver
    2007
    Posts
    233

    If there is nothing review the formula for 0 value

    hi to all,
    i have a file in which there are some works. in sheet 1 column c is work planned date and column d is actual work done date. and column e is the difference between planed date and actual date named as delay. in sheet 2 there are weekly scoring according to planned date and actual date.

    for example:- if any work has to be done on 10/8/2019 (this is our planned date). but the work is getting done on 10/12/2019. so the delay time is 4 days and this will review in sheet 2 how much % the work is getting late and there were how many works in any particular week..

    my real problem is that when there is no planned date or actual date then it shows #N/A in that particular week.( please see AI4, AI5 and there are many more.) i want that this error should not be shown up if there are not any work its should show up the value 0 but don't want to use iferror function. because when we use -iferror it shows 0 but my formula auto calculate in minus. so it starts to show up the result in plus. (without -if error result is coming -75%, and with -iferror result comes 75% which is wrong.) so i want any change in formula that it can show up 0 if there is not any work in any particular week and want this thing for all the #N/A. https://docs.google.com/spreadsheets...#gid=858972579

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: If there is nothing review the formula for 0 value

    akshay6s it is expedient to upload sample files directly to this forum.

    Some members are wary of 3rd party hosting sites and they often have company policies prohibiting such.

    - this should be a cut-down version of your real data, but with the same layout and containing enough data to demonstrate the difficulties that you are facing.

    If you are not familiar with how to do this ...
    To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

    be sure to desensitize the data

    click “Go Advanced” (next to Post Quick Reply – bottom right),
    scroll down until you see “Manage Attachments”, click that,
    click “Browse”.
    select your file(s)
    click “Upload”
    click “Close window”
    click “Submit Reply”

    The file name will appear at the bottom of your reply.

    (Of note: The 'paperclip' icon has not worked for some time. So please save yourself some grief.)
    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: If there is nothing review the formula for 0 value

    Agree a sample file uploaded here will help, but it sounds to me you need something like this...

    =if(cell-with-date="","",your-formula)
    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
    08-07-2018
    Location
    india
    MS-Off Ver
    2007
    Posts
    233

    Re: If there is nothing review the formula for 0 value

    thanks for replying and i have tried to upload but this is google sheet and when i am clicking on upload after pasting my sheet's url in choose file section. it starts to show invalid file and i have posted my file in google sheets, mac, os, others section. and don't know how to fit your suggestion in my formula. please visit my sheet.

  5. #5
    Forum Contributor
    Join Date
    08-07-2018
    Location
    india
    MS-Off Ver
    2007
    Posts
    233

    Re: If there is nothing review the formula for 0 value

    i have already tried to upload. this is showing invalid file. what more can i do. and how a google sheet can mislead anyone while there are already 84 views of my file. same i have posted in google help forum also. there is no third party issue. the why here.

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: If there is nothing review the formula for 0 value

    Quote Originally Posted by akshay6s View Post
    i have already tried to upload. this is showing invalid file. what more can i do. and how a google sheet can mislead anyone while there are already 84 views of my file. same i have posted in google help forum also. there is no third party issue. the why here.
    akshay6s
    i have already tried to upload. this is showing invalid file.
    Have you followed the steps in post #2? Or are you using the paperclip button (that doesn't work)?

    My post RE 3rd parties is in the spirit of friendly information only. Sorry if I was not clear on that.

    There is no forum rule or issue regarding 3rd party sites. Many contribute from their places of employment. They often have policies and software preventing these. The fact that you have done this before with no issues is more indicative that no one raised the point or people who responded do not have an issue where they post from.

    In either event please be reassured there are no issues here; you may simply find greater participation if you upload directly to the forum.
    Again my apologies if I gave the impression otherwise and thank you for drawing this to my attention.

    Edited RE:
    on upload after pasting my sheet's url in choose file
    Try saving the file to your computer and then upload that file to the forum.
    Last edited by FlameRetired; 10-15-2019 at 02:10 PM. Reason: added detail

  7. #7
    Forum Contributor
    Join Date
    08-07-2018
    Location
    india
    MS-Off Ver
    2007
    Posts
    233

    Re: If there is nothing review the formula for 0 value

    from your reply anyone can know that you are really kind hearted person. i always uploads excel files. but this is google sheet. if i download this the query function starts to show dummy function and so no one can understand anything. that's why i was sharing my sheet. and that was google docs section and i shared the same in google help forum also. they always prefer link sharing for google sheets. and i was trying to upload my file where the page shows "upload from a website" i just pasted my url there and clicked on upload as we do the same after selecting excel file from our computer drive then upload, but that started to show invalid file. thanks again for being so humble to me and i really liked to have a conversation with you. thanks.

  8. #8
    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: If there is nothing review the formula for 0 value

    Did you try my suggestion?

  9. #9
    Forum Contributor
    Join Date
    08-07-2018
    Location
    india
    MS-Off Ver
    2007
    Posts
    233

    Re: If there is nothing review the formula for 0 value

    no. i have not tried. sorry for that, because i don't know how to put that in the existing formula. if you can visit my sheet then please can you do it for me.

  10. #10
    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: If there is nothing review the formula for 0 value

    My suggestion was...
    =if(cell-with-date="","",your-formula)

    This means that you meed to test the cell with the date in. If that cell is empty (""), then show nothing "", if not, then use your existing formula.

  11. #11
    Forum Contributor
    Join Date
    08-07-2018
    Location
    india
    MS-Off Ver
    2007
    Posts
    233

    Re: If there is nothing review the formula for 0 value

    thanks for reply, but my data revolves around two dates. first is week starting like 16-10-2019 and second is weekend 23-10-2019. all the work between these two dates will get treated. then which date have to be put there.

  12. #12
    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: If there is nothing review the formula for 0 value

    Based on your initial request...
    my real problem is that when there is no planned date or actual date then it shows #N/A in that particular week.
    Assume planned is in A 1 and Actual is in B1.
    if you need to see if both are empty...
    =if(and(a1="", B1=""),"",your-formula)

    If you need to see if eith er 1 is empty...
    =if(or(a1="", B1=""),"",your-formula)

  13. #13
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: If there is nothing review the formula for 0 value

    I don't understand why you don't want to use IFERROR. Using:


    =iferror(round(query(SB," sel ect count(D)/count(C) where C>=date '"&TEXT(G1,"yyyy-mm-dd")&"' and C<=date '"&TEXT(G2,"yyyy-mm-dd")&"' label count(D)/count(C) ''")*100-100,2)&"%",0)

    for example will return 0% rather than your current #N/A error.

    (Note: I had to put a space in the word "Select" so that the firewall would let me post the formula)
    Rory

+ 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. Help in Excel Date Formula review
    By vbvba in forum Excel General
    Replies: 2
    Last Post: 04-12-2019, 11:05 AM
  2. Can somebody review my formula - it doesn't look right to me.
    By YMS_1975 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 10-04-2017, 01:40 PM
  3. [SOLVED] Automatic calculation formula car review
    By Berna11 in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 01-21-2016, 01:18 PM
  4. =IF([@Review]<>"",VALUE(LEFT([@Review],1)),0) Explaination.
    By XoniaDao in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-09-2015, 03:01 AM
  5. Review of Index and Match Formula
    By ratcat in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-05-2008, 07:05 AM
  6. [SOLVED] Formula for reminder of data needing review on dates?
    By Scorp in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-13-2006, 03:10 AM
  7. Replies: 2
    Last Post: 09-06-2005, 07:05 AM

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