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 formula help

Discussion in 'Business Applications' started by irvinbang, Jan 30, 2013.

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

    irvinbang Thread Starter

    Joined:
    Jan 25, 2013
    Messages:
    85
    if i have a formula in a cell ex:=IF(H7>0U9,"") the ,"" makes the cell empty untill something gets put into H7. Now the problem when i try to =sum the numbers in that column it gives me a error. I want to be able to add the cells with no numbers but has =IF(H7>0U9,"") in it, and if there is a number in there the =sum works just fine. Its only when the cells with that formula is left blank that it gives me the error. Any idea's? Thanks in advance.
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    that should work and add the cells - but you need to complete the IF statement and add the false condition

    =IF(H7>0U9,"", false)
     
  3. irvinbang

    irvinbang Thread Starter

    Joined:
    Jan 25, 2013
    Messages:
    85
    the false thing didnt work. just said error.
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    would you post a sample spreadsheet with examples of what you would like the results to show please
     
  5. irvinbang

    irvinbang Thread Starter

    Joined:
    Jan 25, 2013
    Messages:
    85
    Sure thing sorry about that. forgot to put the file! Hope this helps and good luck. Now i know that i can just put ,"0" and it will auto add in the areas shown but i do not want to cell to show a 0. i have my reasons as to why the cell needs to be blank just not sure how to work the formula. Thanks for the help in advance.
     

    Attached Files:

  6. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    the addition will result in #value errors
    and as you have tried using sum to overcome that - BUT you are using sum with the addition sign

    try using
    a , instead of +
    =SUM(D10,D11,D12,D13,D14,D15,D19,D20,D22,D24,D27)

    have a read here
    http://support.microsoft.com/kb/100825
     
  7. irvinbang

    irvinbang Thread Starter

    Joined:
    Jan 25, 2013
    Messages:
    85
    i tried that as well. still gives me the same #value errors. because there is no 0 in the blank areas that i need added it gives me the errors. but some times there are numbers in that area and in that case it works fine but when there is no numbers and its blank i still need it to add the other areas.
     
  8. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    worked ok for me

    I added thick2 in b9 to get blanks

    see attached
     

    Attached Files:

  9. irvinbang

    irvinbang Thread Starter

    Joined:
    Jan 25, 2013
    Messages:
    85
    Well i guess i could ask my question another way. If i was to put any thing into the thick 2 area. i want all the 0's in the left column to go away. Not sure if this would be a little more easy than the other question i asked. I have attached the file to show an example.
     

    Attached Files:

  10. irvinbang

    irvinbang Thread Starter

    Joined:
    Jan 25, 2013
    Messages:
    85
    i know that adding the thick2 in there clears the numbers out. I wanted to add the numbers that was there not get rid of them. but i asked the question another way. and reposted the file if you could take a look at that maybe the question this time is a little more easy and upfront on what i would like to see done. thanks for your help and input!
     
  11. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    the zeros are set by this formula in most of the cells
    =IF(D7>0,U6,"0")

    and "0" is text anyway and not a number
    hence the reason you are getting zeros as text and some errors

    change those formulas to use "" instead of "0"
    =IF(D7>0,U6,"")

    adjust the total in D17 to use , and not +

    change
    =SUM(U11+U12+U13+U14)

    to
    =SUM(U11,U12,U13,U14)
    and add an if
    =IF(SUM(U11,U12,U13,U14)=0, "", SUM(U11,U12,U13,U14))
     
  12. irvinbang

    irvinbang Thread Starter

    Joined:
    Jan 25, 2013
    Messages:
    85
    Thanks very much for that. Guess this is getting more complicated than i thought. That fixed my question about making the 0's go away. but that didnt fix my question that i was trying to find out. thought by doing the 0's it would fix it. the way this document works is that if you have a Work Order number. the work order number can go into three different fields. you can have an EXINSP work order number that could go into D7 and you could have an EXINSP & INSLIN wo# at the same time, and you can have a THICK2 wo#. Now you will never have a THICK2 with on of the other work order numbers. that is why its set up that when you put a number into the thick2 area K7 it removes all the other numbers. Now if you put wo# into EXINSP it will auto calculate hours, if you put EXINSP and INSLIN it will auto calculate the number differently pasted upon the inspection that is being preformed. Now If there is an EXINSP and INSLIN there is nothing wrong with the spreedsheet and evertyhing works just fine and the hours auto calculate down in E32 for the areas i need to auto calculate. Now if i just have an EXINSP those hours dont calculate due to the formulas in the cells that are missing the INSLIN numbers. but they will be blank do to no INSLIN wo needed at this time. Now i know that putting in the thick will remove this and add up the numbers but If there is an EXINSP there will never be a thick so that cant be in there if there is an EXINSP. So now with a little back ground on the operation of this spreedsheet i would like for E37 to auto calculate the cells in its current formula even if there is nothing in the cells due to there not being an INSLIN. i also want it to auto calculate if there is a EXINSP & INSLIN. And if there is a THICK2 i want it to remove all the area's in that colum. Make any sence?
     
  13. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    Happy to go through and get correct
    BUT lets understand the rules you need , also try to split out the paragraph quite difficult on a forum to read that paragraph

    you have two sheets- Normal and RAC - are these the same or are the rules different on each sheet
    SO
    we have three possible words entered into some cells

    EXINSP
    INSLIN
    THICK2

    so re-read the paragraph and still not sure

    We can work out a formula to add to the cells so that it works as required

    Work No# can go into the cells
    D7 EXINSP
    H7 INSLIN
    K7 THICK2

    and THICK2 K7 will never exist if either D7 or H7 is completed
    and D7 and H7 can be populated with one or both

    there are numbers in D10 - D32

    I think I worked out that

    but appears you are using B9

    FROM THE NORMAL SHEET - as the RAC sheet using different Cells it would appear



    D10 = "" if B9 = thick2
    D11 = "" if D7 does not contain a work number for EXINSP
    D12 = "" if B9 = thick2
    D13 = "" if B9 = thick2
    D14 = "" if B9 = thick2
    D15 = "" if H7 does not contain a work number for INSLIN
    D16 = "" if B9 = thick2
    D17 = Sum of numbers - so will this always add up or if likely to be 0 then you need the IF formula I posted earlier
    D18 = This is testing H6 ?? for footage - so again how to make zero
    D19 = "" if H7 does not contain a work number for INSLIN
    D20 = "" if H7 does not contain a work number for INSLIN
    D21 = "" if B9 = thick2
    D22 = "" if B9 = thick2
    D23 = "" if B9 = thick2
    D24 = "" if B9 = thick2
    D25 = "" if B9 = thick2
    D26
    D27 = "" if B9 = thick2
    D28 = "" if B9 = thick2
    D29
    D30


    Am i anywhere near ?
    OR could you define the rules BUT not in 1 paragraph

    this may take a few itterations
     
  14. irvinbang

    irvinbang Thread Starter

    Joined:
    Jan 25, 2013
    Messages:
    85
    Ok yeah makes better since the way you put things. Sorry about the one paragraph i can see how that would be irritating. Lets go off the format that you had.

    I have two different sheets Normal and RAC - the only thing different from on and the other is the RAC has Sketch Drawings on it. The hours for this will aways be 12 and will need to be included in the total E33

    There are three possible words entered into some cells

    EXINSP
    INSLIN
    THICK2

    Work No# can go into the cells
    D7 EXINSP can at times be the only wo#
    H7 INSLIN will always have an EXINSP wo# too
    K7 THICK2 will never have an EXINSP or INSLIN wo#

    THICK2 K7 will never exist if either D7 or H7 is completed
    D7 and H7 can be populated with one or both.

    there are numbers in D10 - D30 on Normal sheet and there are numbers D10 - D31 on RAC sheet.

    The only reason B9 has a formula in it is so that when you enter in a wo# into the Thick2 K7 it will say "THICK2" in B9. And if B9 has Thick2 then there is no Inspector name assigned to this Circuit. If EXINSP, or INSLIN has a wo# in there cells then the page is just printed out and the scheduler will take and hand right in the inspector name for that circuit.

    FROM THE NORMAL SHEET

    D10 = "" if B9 = thick2 \ will always be 1 hour.
    D11 = "" if D7 does not contain a work number for EXINSP \ will auto calculate by a % of the footage.
    D12 = "" if B9 = thick2 \ will always be 2 hours
    D13 = "" if B9 = thick2 \ will always be 2 hours
    D14 = "" if B9 = thick2 \ will always be 2 hours
    D15 = "" if H7 does not contain a work number for INSLIN \ will auto calculate when an INSLIN wo# is added
    D16 = "" if B9 = thick2 \ will always be 2 hours
    D17 = Sum of U11,U12,U13,U14 \ is auto calculated by a % of the Fitting # entered into N7,N9
    D18 = Sum(D17*20%) its a percent of the hours in D17
    D19 = "" if H7 does not contain a work number for INSLIN \ is a % based off the footage in H6
    D20 = "" if H7 does not contain a work number for INSLIN \ is a % based off the hours in D19
    D21 = "" if B9 = thick2 \ will always be 3 hours
    D22 = "" if B9 = thick2 \ will alwyas be 2 hours
    D23 = "" if B9 = thick2 \ will always be 2 hours
    D24 = "" if B9 = thick2 \ will always be 1 hour
    D25 = "" if B9 = thick2 \ will always be 2 hours
    D26
    D27 = "" if B9 = thick2 \ will always be 1 hour
    D28 = "" if B9 = thick2 \ will always be 2 hours
    D29 will have a number entered if service is needed but does not need to auto calculate
    D30 will have a number entered if service is needed but does not need to auto calculate
    E32 = The sum of hours for D10:D14,D22,D24,D27 for EXINSP only.
    If INSLIN E32 should be =SUM D10:D15,D19,D20,D24,D27
    \ This formula dont work

    RAC sheet is just the same only difference is
    E33 = The sum of hours for D10:D15,D22,D24,D27 for EXINSP only.
    If INSLIN E32 should be =SUM D10:16,D20,D21,D23,D25,D28
    \ This formula dont work

    NORMAL SHEET THICK2 once a wo # is intered into thick2 K7 then B9 will say THICK2.
    D10,D12,D13,D18,D21:D28 will move over to the L column. D11,D15,D17:D20 should have the 0's removed so that the cell is blank. This does not work to remove all the 0's.

    RAC SHEET THICK2 Will never be used ever. the only time the RAC page will be used is when there will be an EXINSP and/or INSLIN.

    I hope this is better than the one paragraph and this helps with helping me fix my document. sorry for all the trouble! thanks again for any help.
     
  15. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,252
    First Name:
    Wayne
    it may be over the weekend before i get back to this - but i think that will help a lot now
     
  16. 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/1087597

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice