Please explain formula to me

[Guest]

I have following formula suggested to me, but I do not understand the
functions of it. To use it in different case, I would neewd to addapt it. can
someone explain it to me please?
=0.25*((40-COUNTBLANK(B3:B40))/2-1)*(COUNTBLANK(B3:B40)<40/24
When I change the ranges the formula returns wrong data.

 

[Fred Smith]

Your problem probably is the 'countblank(b3:b40)<40' part.

This returns true (=1) if there are less than forty blanks in the range, else
false (=0). So you are either multiplying by 1 or 0 depending on how many blanks
are in the range.

As a range of b3:b40 can never have more than 40 blanks, this formula always
evaluates to true (=1), and has no impact on the result. However, if you expand
the range to more than 40 cells, you could get a zero result, because 0 times
anything results in 0.

Is this your problem?

 

[T. Valko]

First thing is you're missing a ")". Since I don't know what your intentions
are with this formula I put the missing ")" at the end of the formula so
that it would at least return something:

=0.25*((40-COUNTBLANK(B3:B40))/2-1)*(COUNTBLANK(B3:B40)<40/24)

Here is how Excel is calculating that formula:

40-COUNTBLANK(B3:B40)
40-COUNTBLANK(B3:B40))/2
40-COUNTBLANK(B3:B40))/2-1
0.25*((40-COUNTBLANK(B3:B40))/2-1)
COUNTBLANK(B3:B40)
COUNTBLANK(B3:B40)<40/24
=0.25*((40-COUNTBLANK(B3:B40))/2-1)*(COUNTBLANK(B3:B40)<40/24)

The steps Excel takes to arrive at a result can mean the difference between
the correct result and an incorrect result.

Biff