Hi,

Im not quite new at using Excel, but I have decided to try to take on new
challenges and I need some help in relation to that.

#1:
I want to make some calculations on 4 values, lets say that they are located
in B3:B6. I to find out if each value exceeds the number 40, and if it does
the exceeding ammount should be added in B7. For instance if the values are
30/41/60/10 then B7 would show the number "21". How is this possible?

#2:
If I have B2:B6 looking like this again:
B3 30
B4 40
B5 60
B6 10
Total of 140.
And I want to add 15 ponts in total to these numbers, but it should be done
according to the % distribution, so that B3 would get 21.4% of the 15 points,
B4 would get 28.7% of the 15 points and so on. I want it to be added in a new
collum. Is this possible?

Thanks in advance

1: =SUMPRODUCT((B3:B640)*(B3:B6-40))
2: =B3+B3/SUM($B$3:$B$6)*15 and fill down.
Bob Umlas
Excel MVP

"KristopherJensen" wrote in
message ...
Hi,

Im not quite new at using Excel, but I have decided to try to take on new
challenges and I need some help in relation to that.

#1:
I want to make some calculations on 4 values, lets say that they are
located
in B3:B6. I to find out if each value exceeds the number 40, and if it
does
the exceeding ammount should be added in B7. For instance if the values
are
30/41/60/10 then B7 would show the number "21". How is this possible?

#2:
If I have B2:B6 looking like this again:
B3 30
B4 40
B5 60
B6 10
Total of 140.
And I want to add 15 ponts in total to these numbers, but it should be
done
according to the % distribution, so that B3 would get 21.4% of the 15
points,
B4 would get 28.7% of the 15 points and so on. I want it to be added in a
new
collum. Is this possible?

Thanks in advance

Try this:

#1:
Two ways....
B7: =SUMIF(B3:B6,"40",B3:B6)-COUNTIF(B3:B6,"40")*40
or
B7: =SUMPRODUCT(--(B3:B640)*(B3:B6-40))

#2:
C3: =B3+(B3/$B$7)*15
(copy that formula down thru C6)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"KristopherJensen" wrote:

Hi,

Im not quite new at using Excel, but I have decided to try to take on new
challenges and I need some help in relation to that.

#1:
I want to make some calculations on 4 values, lets say that they are located
in B3:B6. I to find out if each value exceeds the number 40, and if it does
the exceeding ammount should be added in B7. For instance if the values are
30/41/60/10 then B7 would show the number "21". How is this possible?

#2:
If I have B2:B6 looking like this again:
B3 30
B4 40
B5 60
B6 10
Total of 140.
And I want to add 15 ponts in total to these numbers, but it should be done
according to the % distribution, so that B3 would get 21.4% of the 15 points,
B4 would get 28.7% of the 15 points and so on. I want it to be added in a new
collum. Is this possible?

Thanks in advance

"KristopherJensen" wrote:

#1:
I want to make some calculations on 4 values, lets say that they are
located
in B3:B6. I to find out if each value exceeds the number 40, and if it
does
the exceeding ammount should be added in B7.
For instance if the values are 30/41/60/10 then
B7 would show the number "21". How is this possible?

Put in B7: =SUMPRODUCT(--(B3:B640),B3:B6-40)

Or, perhaps better to point to a cell instead of hardcoding the "40"

Put in say, B8: 40
Then put in B7: =SUMPRODUCT(--(B3:B6B8),B3:B6-B8)

#2:
If I have B2:B6 looking like this again:
B3 30
B4 40
B5 60
B6 10
Total of 140.
And I want to add 15 ponts in total to these numbers, but it should be
done
according to the % distribution, so that B3 would get 21.4% of the 15
points,
B4 would get 28.7% of the 15 points and so on. I want it to be added in a
new
column. Is this possible?

Put in C2: 15
Put in C3: =B3+15*(B3/SUM($B$3:$B$6))
Copy C3 down to C6
Format C3:C6 to 1 d.p.

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Oops, correction:
Put in C3: =B3+15*(B3/SUM($B$3:$B$6))

should be:
Put in C3: =B3+$C$2*(B3/SUM($B$3:$B$6))

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

KristopherJensen Wrote:
Hi,

Im not quite new at using Excel, but I have decided to try to take on
new
challenges and I need some help in relation to that.

#1:
I want to make some calculations on 4 values, lets say that they are
located
in B3:B6. I to find out if each value exceeds the number 40, and if it
does
the exceeding ammount should be added in B7. For instance if the values
are
30/41/60/10 then B7 would show the number "21". How is this possible?

#2:
If I have B2:B6 looking like this again:
B3 30
B4 40
B5 60
B6 10
Total of 140.
And I want to add 15 ponts in total to these numbers, but it should be
done
according to the % distribution, so that B3 would get 21.4% of the 15
points,
B4 would get 28.7% of the 15 points and so on. I want it to be added in
a new
collum. Is this possible?

Thanks in advance

Here is very simple, no frills solution:

#1

I suggest a helper column (that you can hide). Enter this formula in
Cell C3,

=IF(B340,B3-40,\"\")

and copy down until Cell C6.

Enter this formula in Cell B7, *=sum(C3:C6)*

#2

In any unused cell, enter the number you want to add (in your example
-- 15)
to your entries in Cells B3:B6,

Let's say you entered this number in Cell E3. In Cell D3, enter this
formula,

=B3+(B3/$B$7)*$E$3

and copy down until Cell D6.

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=497282

#1
=MAX(B3-40,0)+MAX(B4-40,0)+MAX(B5-40,0)+MAX(B6-40,0)
#2
=15*(B3/SUM($B$3:$B$6)) in C3, filled down to C6

--
Kind regards,

Niek Otten

"KristopherJensen" wrote in
message ...
Hi,

Im not quite new at using Excel, but I have decided to try to take on new
challenges and I need some help in relation to that.

#1:
I want to make some calculations on 4 values, lets say that they are
located
in B3:B6. I to find out if each value exceeds the number 40, and if it
does
the exceeding ammount should be added in B7. For instance if the values
are
30/41/60/10 then B7 would show the number "21". How is this possible?

#2:
If I have B2:B6 looking like this again:
B3 30
B4 40
B5 60
B6 10
Total of 140.
And I want to add 15 ponts in total to these numbers, but it should be
done
according to the % distribution, so that B3 would get 21.4% of the 15
points,
B4 would get 28.7% of the 15 points and so on. I want it to be added in a
new
collum. Is this possible?

Thanks in advance