| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum column and calculate numbers 0 in the same column with 1,5
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum column and calculate numbers 0 in the same column with 1,5
Try this:
=SUMIF(B2:B33,"0")*1,5 Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Gutti" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum column and calculate numbers 0 in the same column with 1,5
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 "Gutti" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum column and calculate numbers 0 in the same column with 1,5
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) "Ron Coderre" wrote in message ... Try this: =SUMIF(B2:B33,"0")*1,5 Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Gutti" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum column and calculate numbers 0 in the same column with 1,5
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 "Ron Coderre" wrote in message ... 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) "Ron Coderre" wrote in message ... Try this: =SUMIF(B2:B33,"0")*1,5 Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Gutti" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum column and calculate numbers 0 in the same column with 1,5
Hi, Martin
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? 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) "MartinW" wrote in message ... 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 "Ron Coderre" wrote in message ... 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) "Ron Coderre" wrote in message ... Try this: =SUMIF(B2:B33,"0")*1,5 Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Gutti" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum column and calculate numbers 0 in the same column with 1,5
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. ------------------------------------- For 100 instances of each formula --------------------------------- Formula #1 ran in 1.26694 seconds Formula #2 ran in 1.01559 seconds Formula #2 was 0.25135 seconds faster. -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Ron Coderre" wrote in message ... Hi, Martin 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? 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) "MartinW" wrote in message ... 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 "Ron Coderre" wrote in message ... 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) "Ron Coderre" wrote in message ... Try this: =SUMIF(B2:B33,"0")*1,5 Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (Excel) "Gutti" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculate Figures in a column if ajacent column has particular da | Charts and Charting in Excel | |||
| Calculate an Excel column that contains numbers and #Values | Excel Discussion (Misc queries) | |||
| calculate average in a column based on criteria in another column | Excel Discussion (Misc queries) | |||
| how do you calculate a column of numbers - eg. A1 to A20? | Excel Discussion (Misc queries) | |||
| calculate which cells in column A will give me the total of column | Excel Worksheet Functions | |||
Linear Mode
