Need help with writing conditional formulas

[tsabiha]

I have one formula the refers to another and if there are no sample it states
no sample, but because of the text my next formula comes up with an error
(#Value). I want to creat a condition that if no sample is present it comes
up with N/A.
1st formula

=IF(ISERROR((IF((C151+E151)<14,INDEX($L$4:$N$18,C151+E151+1,2),(CHIINV(1-0.6,2*(C151+E151+1))))/(2*G151*ROLSUMRY!$G$11))*1000000000),"no
samples",((IF((C151+E151)<14,INDEX($L$4:$N$18,C151+E151+1,2),(CHIINV(1-0.6,2*(C151+E151+1))))/(2*G151*ROLSUMRY!$G$11))*1000000000))

2nd formula
=1000000000/(H151*24*365)

 

[Bob Greenblatt]

I have one formula the refers to another and if there are no sample it states
no sample, but because of the text my next formula comes up with an error
(#Value). I want to creat a condition that if no sample is present it comes
up with N/A.
1st formula

=IF(ISERROR((IF((C151+E151)<14,INDEX($L$4:$N$18,C151+E151+1,2),(CHIINV(1-0.6,2
*(C151+E151+1))))/(2*G151*ROLSUMRY!$G$11))*1000000000),"no
samples",((IF((C151+E151)<14,INDEX($L$4:$N$18,C151+E151+1,2),(CHIINV(1-0.6,2*(
C151+E151+1))))/(2*G151*ROLSUMRY!$G$11))*1000000000))

2nd formula
=1000000000/(H151*24*365)

You'll get a value error if any of the referenced fields are in error. I
think it will be easier to understand if you use:
=if(or(iserror(c151),iserror(e151),iserror(g151)),NA(),if(c151+e151<14,.....
......

 

[tsabiha]

This is not working, the first formula is calculating the data in the other
fields and if there are no samples it fills in "no sample".

the second fomula uses the results of the first formula. I only have a
proplem when there are no sample, it works as long as a number is placed
instead of text.

The workbook contains about 15 pages that interelates.

 

[Bob Greenblatt]

This is not working, the first formula is calculating the data in the other
fields and if there are no samples it fills in "no sample".

the second fomula uses the results of the first formula. I only have a
proplem when there are no sample, it works as long as a number is placed
instead of text.

The workbook contains about 15 pages that interelates.

Then, I don't think you understood my response. You can rewrite your formula
using the technique I described to check for errors. It will place #NA if
there is an error and "no sample" in that case, and the calculation if
neither. Look again at what I proposed. If you are still having trouble,
post the new formula here and I'll try to help further.

 

[tsabiha]

=1000000000/(H151*24*365)=if(or(iserror(c151),iserror(e151),iserror(g151)),NA(),if(c151+e151<14))

This is what I put in. I need the formula (when there are samples) to do the
calulations, only when there is "no sample" do I want it to come up with N/A

Is there a way to send an attachement so you can see what I am working on.

 

[tsabiha]

Block H151 is the 1st formula and Block I151 has the second formula.
It is only the second formula Block I151 that I need to state N/A. H still
need to state no samples

 

[Carl Witthoft]

Can you post examples of what's in the cells that you are checking with
ISERROR() ? I lost the beginning of this thread. A blank cell is not
an error; beyond that I'm not sure what's going on.

 

[Bob Greenblatt]

=1000000000/(H151*24*365)=if(or(iserror(c151),iserror(e151),iserror(g151)),NA(
),if(c151+e151<14))

This is what I put in. I need the formula (when there are samples) to do the
calulations, only when there is "no sample" do I want it to come up with N/A

Is there a way to send an attachement so you can see what I am working on.

I can not see your original post. But the formula above is completely wrong.
What you need is something like:
=if(or(iserror(c151),iserror(e151),iserror(g151)),NA(),if(c151+e151<14),"no
samples",1000000000/(H151*24*365))

You must remember that in Excel, IF statements read:
=IF(expression,value if True, value if false)

 

[tsabiha]

First post

I have one formula the refers to another and if there are no sample it states
no sample, but because of the text my next formula comes up with an error
(#Value). I want to creat a condition that if no sample is present it comes
up with N/A.
1st formula

=IF(ISERROR((IF((C151+E151)<14,INDEX($L$4:$N$18,C151+E151+1,2),(CHIINV(1-0.6,2*(C151+E151+1))))/(2*G151*ROLSUMRY!$G$11))*1000000000),"no
samples",((IF((C151+E151)<14,INDEX($L$4:$N$18,C151+E151+1,2),(CHIINV(1-0.6,2*(C151+E151+1))))/(2*G151*ROLSUMRY!$G$11))*1000000000))

2nd formula
=1000000000/(H151*24*365)
--
3rd post

Block H151 is the 1st formula and Block I151 has the second formula.
It is only the second formula Block I151 that I need to state N/A. H still
need to state no samples

I do not care what is in any other block, I am only care about "h" and "I"
I tried:
=if(or(iserror(c151),iserror(e151),iserror(g151)),NA(),if(c151+e151<14),"no
samples",1000000000/(H151*24*365))

and it came up with errors also. again, "C", "E" "G" are not relavant. The
problem comes up when the "No sample" from the first formula is posted in
"H", my formula in "I" it can not compute the text, it wants a number. I need
it to come up with, if text, (N/A),