I am entering projections on a worksheet
example $100 in cell 1 $200 in cell 2 ect.

I have a chart that states the criteria needed for each amount entered
example $0-$78 = 1
$79-200= 2
ect ect.

i need a formula where i can enter the projections in one cell and
based on those projections and the chart give the number for those is
this possible ?


--
darkbearpooh1
------------------------------------------------------------------------
darkbearpooh1's Profile: http://www.excelforum.com/member.php...o&userid=30640
View this thread: http://www.excelforum.com/showthread...hreadid=502953

Take a look at these notes on SumProduct...

'/-----------------------------------------------------------/
'DATA:
' A B C
'1 Start stop projection
'2 0 78 1
'3 79 200 2
'4 201 500 3
'5 501 1000 4
'/-----------------------------------------------------------/
'FORMULAS: Where Score is the value being looked up and Projection is the
looked up value...
' A B
' 7 Score Projection using SumProduct formula
' 8 400 3 =SUMPRODUCT(--(A8=A2:A5),--(A8<=B2:B5),--(C2:C5))
' 9 15 1 =SUMPRODUCT(--(A8=A2:A5),--(A8<=B2:B5),--(C2:C5))
'10 799 4 =SUMPRODUCT(--(A10=A2:A5),--(A10<=B2:B5),--(C2:C5))
'11 82 2 =SUMPRODUCT(--(A11=A2:A5),--(A11<=B2:B5),--(C2:C5))
'/-----------------------------------------------------------/

'SumProduct muliplies the 1st array * 2nd array * 3rd array * etc
' and then adds the products
'
'FALSE = 0 / TRUE = 1
'
'In the DATA above, SumProduct evaluates the 400 in Cell A8 as
'FALSE * FALSE * 1
'FALSE * FALSE * 2
'TRUE * TRUE * 3
'FALSE * FALSE * 4
'
'OR
'
' 0 * 0 * 1 = 0
' 0 * 0 * 2 = 0
' 1 * 1 * 3 = 3
' 0 * 0 * 4 = 0
' ---
' 3
' ===
'/-----------------------------------------------------------/
'NOTES:
' The double dash "--" is used because you may be summing a range
'that could contain non-numeric text or Booleans. This will coerce
'the returns to 0 or 1. Otherwise, #VALUE! may be returned.
'/-----------------------------------------------------------/



HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"darkbearpooh1" wrote:


I am entering projections on a worksheet
example $100 in cell 1 $200 in cell 2 ect.

I have a chart that states the criteria needed for each amount entered
example $0-$78 = 1
$79-200= 2
ect ect.

i need a formula where i can enter the projections in one cell and
based on those projections and the chart give the number for those is
this possible ?


--
darkbearpooh1
------------------------------------------------------------------------
darkbearpooh1's Profile: http://www.excelforum.com/member.php...o&userid=30640
View this thread: http://www.excelforum.com/showthread...hreadid=502953