Simple Module for Excel 2007 in VBA

  • Thread starter Thread starter ZikO
  • Start date Start date
Z

ZikO

Hello

I tried to calculate in excell somthing like this

+-----+--------------------------------+
| A | B |
+-----+--------------------------------+
| | =SUMIF(POWER(10,A1:A10),"<>") |
+-----+--------------------------------+
| 5 | |
+-----+--------------------------------+
| 10 | |
+-----+--------------------------------+
| | |
+-----+--------------------------------+
| 12 | |
+-----+--------------------------------+

The formula was uspposed to take range of only those cells which contain
numbers. However because its array formula and there is function POWER
inside excel states there's a error in formula.
I just have to avoid empty cells.

I tried VBA but honestly it's at the moment too much for me. In VBA I
tried to build proper Range object, use it to calculate sum according to
that range and then return this value in cell B1

Any suggestion on that.

Thanks
 
=SUM(POWER(10,$A$1:$A$10))-COUNTIF($A$1:$A$10,"")
(array formula)
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html

..
..
..

"ZikO" <[email protected]>
wrote in message
Hello
I tried to calculate in excell somthing like this

+-----+--------------------------------+
| A | B |
+-----+--------------------------------+
| | =SUMIF(POWER(10,A1:A10),"<>") |
+-----+--------------------------------+
| 5 | |
+-----+--------------------------------+
| 10 | |
+-----+--------------------------------+
| | |
+-----+--------------------------------+
| 12 | |
+-----+--------------------------------+

The formula was uspposed to take range of only those cells which contain
numbers. However because its array formula and there is function POWER
inside excel states there's a error in formula.
I just have to avoid empty cells.

I tried VBA but honestly it's at the moment too much for me. In VBA I
tried to build proper Range object, use it to calculate sum according to
that range and then return this value in cell B1
Any suggestion on that.
Thanks
 
=SUM(POWER(10,$A$1:$A$10))-COUNTIF($A$1:$A$10,"")
(array formula)
Click to expand...
Thanks for that but I don't understand why I would have to put COUNTIF()
formula in that equation?
 
N^0 equals 1, so you will have one added to the total for each blank cell.
Click to expand...

Jim I really appreciate your answers but I am afraid I still cannot
figure out why is like that :( Would you like to elaborate somehow
this specific problem so I could understand it. Many thanks for that.
 
A1 = 5
A2 = 10
A3 is blank (treated as a zero)
A4 = 12
A5 is blank (treated as a zero)
'---
10^A1 = 100,000
10^A2 = 10,000,000,000
10^A3 = 1
10^A4 = 1,000,000,000,000
10^A5 = 1
'---
Total = 1,010,000,100,002

So...
Total - CountBlank(A1:A5) = 1,010,000,100,000
--
Jim Cone
Portland, Oregon USA

..
..

"ZikO" <[email protected]>
wrote in message
N^0 equals 1, so you will have one added to the total for each blank cell.
Click to expand...

Jim I really appreciate your answers but I am afraid I still cannot
figure out why is like that :( Would you like to elaborate somehow
this specific problem so I could understand it. Many thanks for that.
 
A1 = 5
A2 = 10
A3 is blank (treated as a zero)
A4 = 12
A5 is blank (treated as a zero)
'---
10^A1 = 100,000
10^A2 = 10,000,000,000
10^A3 = 1
10^A4 = 1,000,000,000,000
10^A5 = 1
'---
Total = 1,010,000,100,002

So...
Total - CountBlank(A1:A5) = 1,010,000,100,000
Click to expand...

Thanks. It's clear now
 
Back
Top