Ignoring N/A

[cynster76]

How would I ignore the N/As in the below formula?

Thanks.

=((160.5/D3)*1/3)+((80.5/G3)*1/3)+((15.5%/J3)*1/3)

 

[Aladin Akyurek]

How would I ignore the N/As in the below formula?

Thanks.

=((160.5/D3)*1/3)+((80.5/G3)*1/3)+((15.5%/J3)*1/3)

Do you mean that you have #N/A (not N/A) in D3, G3, or J3?

 

[cynster76]

Hi Aladin,

I actually changed it all to N/A. Some had #VALUE others #N/A. How would I get the formula to ignore the non-numbers?

Thanks,
Cynthia

 

[cynster76]

Actually, I meant to say that the one or 3 (in bold), could end up with no value because of missing data. I want the formula to calculate based on the other 2.

How would I go about doing this?

=((160.5/D3)*1/3)+((80.5/G3)*1/3)+((15.5%/J3)*1/3)

 

[Scott Huish]

Multiplying by 1/3 is the same as dividing by 3, so I did change that:

=(IF(AND(D3,ISNUMBER(D3)),(160.5/D3)/3))+IF(AND(G3,ISNUMBER(G3)),((80.5/G3)/3))+(IF(AND(J3,ISNUMBER(J3)),(15.5%/J3)/3))

 

[Rhino373]

Really its the value of D3, G3 or J3 causing the #N/A. The rest are hard coded numbers. Your statement would start with something like this

=IF(ISERR((160.5/D3)*(1/3)),0,(160.5/D3)*(1/3))

This is only a third of your statement and they are separated with plus signs. Also in your statement you write

*1/3

This means multiply by 1 and then divide by 3. I'm guessing you are wanting to multiply by one-third. You'll need brackets around 1/3 to do that. Otherwise there is no point in multiplying by 1.

 

[Aladin Akyurek]

Actually, I meant to say that the one or 3 (in bold), could end up with no value because of missing data. I want the formula to calculate based on the other 2.

How would I go about doing this?

=((160.5/D3)*1/3)+((80.5/G3)*1/3)+((15.5%/J3)*1/3)

Why do we have a percentage as nominator in:

15.5%/J3

while other parts are different?

 

[Rhino373]

Why do we have a percentage as nominator in:

15.5%/J3

while other parts are different?

I'd say its just a typo as the percentile is the number 5 key and the slash is right next to the shift.

 

[cynster76]

Thanks HOTPEPPER, the formula worked. Rhino/Aladin, appreciate your assistance as well.

Have a great day!

 

[Aladin Akyurek]

Actually, I meant to say that the one or 3 (in bold), could end up with no value because of missing data. I want the formula to calculate based on the other 2.

How would I go about doing this?

=((160.5/D3)*1/3)+((80.5/G3)*1/3)+((15.5%/J3)*1/3)

I'd say its just a typo as the percentile is the number 5 key and the slash is right next to the shift.

In that case:

=(1/3)*SUM(IF(N(D3),160.5/D3),IF(N(G3),80.5/G3),IF(N(J3),15.5/J3))

would also the job.