sum column and calculate numbers >0 in the same column with 1,5

G

Gutti

I have numbers >0 and <0 in a column B2 to B33. I have to multiply the
numbers >0 with 1,5 and add the column. Please help.
Gutti
 
R

Ron Coderre

Try this:

=SUMIF(B2:B33,">0")*1,5

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
M

MartinW

Hi Gutti,

Try
=SUMIF(B2:B33,">0")*1.5+SUMIF(B2:B33,"<0")

Looks like you are using a European setup so you
will have to alter the separators to suit.

HTH
Martin
 
R

Ron Coderre

If Martin interpreted your post correctly,
try this:

=SUM(SUMIF(B2:B33,">0")*0.5,B2:B33)

Does that help?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
M

MartinW

And the difference would be?

Don't get me wrong I'm all for streamlining formulae, but when
you are just talking about two ways around a square. Why bother?

Regards
Martin
 
R

Ron Coderre

Hi, Martin

RE:
And the difference would be?

Don't get me wrong I'm all for streamlining formulae, but when
you are just talking about two ways around a square. Why bother?
Click to expand...

I'm all for shorter, more efficient formulas...but not so fanatical that I'd
propose a solution that is so arcane that hardly anybody could figure it
out, and nobody would remember how to do it.

In this case, though, the SUMIF checks every cell in the range to see if it
matches. Since there's no compelling need to do that twice....I offered an
alternative with a bit less overhead.

On small ranges, the difference is negligible.
On large ranges...it may be significant.
(For my own curiosity, I'll compare the times when I get a chance)

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
R

Ron Coderre

For anybody who might be curious:
I just ran the MicroTimer program from MSDN (Charles Williams' code).

Using the FullCalcTimer (worst case)
Formula #1: =SUMIF(D1:D52810,">0")*1.5+SUMIF(D1:D52810,"<0")
Formula #2: =SUM(SUMIF(D1:D52810,">0")*0.5,D1:D52810)

-------------------------------------
For a single instance of each formula
-------------------------------------
Formula #1 ran in 0.04068 seconds
Formula #2 ran in 0.01034 seconds
Formula #2 was 0.03034 seconds faster.
 
Top