Solved: Excel formula help

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 

etaf

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

=IF(H7>0U9,"", false)
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,454
would you post a sample spreadsheet with examples of what you would like the results to show please
 

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.
 

Attachments

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,454
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
 

irvinbang

Thread Starter
Joined
Jan 25, 2013
Messages
85
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
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.
 

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.
 

Attachments

irvinbang

Thread Starter
Joined
Jan 25, 2013
Messages
85
worked ok for me

I added thick2 in b9 to get blanks

see attached
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!
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,454
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))
 

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?
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,454
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
 

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.
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,454
it may be over the weekend before i get back to this - but i think that will help a lot now
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top