Calculate only with cells which contain numbers

[mkrkac]

Dear all!
I couldn't find the solution so far, so I write a new thread (hope it is on the right place).

I am preparing some automatic form for field data.
The problem is that I wont to calculate only with cells which contain numbers.
Please see attachment.
For example, if I measure 4 sets of fractures in the field and later I want to calculate some trigonometric functions,
how can I use only cells which contain this filed data - of course automatically?
In example, you can see that trigonometric functions are calculated also for empty cells,
and the total result is wrong, because for example Cos0=1, but in fact there is no data for this empty cells.

hope you understand me

[BB1972]

Hey mkrac,

You could tell Excel not to count "1" values in your sum formula like this:

Please Login or Register  to view this content.

which gives you the answer you're looking for....

[kbkumar]

Hi - Welcome to the forum. By definition excel treats a blank cell as 0, hence when you apply COS function to a blank cell it returns 1, which though trignometrically is correct but is not the ideal result. I think my assessing the length of the cell we can overcome this.

Try the below in cell B16

Please Login or Register  to view this content.

and this in cell C16:

Please Login or Register  to view this content.

Does this help?

P.S.: From my understanding this will give an error if you just type a space in your data range and this could be overcome by using trim.

[mkrkac]

Dear BB1972,

thanks for replay.
The problem is that field result can also be 0 (A=0 and D=0) and in this case ignoring 1 would be an error

[mkrkac]

Mr kbkumar,

thanks for your suggestion, but your formula is not working in my excel file.
Can you please try it on attached file and than attach it, because I can't find error (not so familiar with If, and...

Thanks

[kbkumar]

Attached. Let me know if this works for you.

[Haseeb Avarakkan]

Since both are in same length, use SUMIF, to check E2:E11 is NOT Blank, then SUM B16:B25

=SUMIF(E2:E11,"<>",B16:B25)

Or SUMPRODUCT with just one table.

=SUMPRODUCT((COS(RADIANS(F2:F11)))*(COS(RADIANS(E2:E11)))*(E2:E11<>""))

[mkrkac]

Mr. kbkumar,

now it works, thank you!

[mkrkac]

Since both are in same length, use SUMIF, to check E2:E11 is NOT Blank, then SUM B16:B25

=SUMIF(E2:E11,"<>",B16:B25)

Or SUMPRODUCT with just one table.

=SUMPRODUCT((COS(RADIANS(F2:F11)))*(COS(RADIANS(E2:E11)))*(E2:E11<>""))

Mr. Haseeb,

thank you for your suggestion and your time, but I attached this table only as an example, in fact I don't need sum of ax, ay, az.
But I learned something :-)
I will play a little bit with table and than attach final version if I succeed to finish it or even if I don't finish it.

Sorry

[kbkumar]

Glad it worked for you. In case this is resolved, please mark the thread as "Solved"