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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Try this:

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

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

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
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
 
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)
 
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
 
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)
 
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.
 
Back
Top