Formula's

[Chris]

I am trying to make a formula where there are six fields
being summed and if the value is between 0-9.5 I want it
to display 1 and if the are between 10-20.5 then 2, 21-
30.3 then 3, 31-44.5 then 4, 45-54.5 then 5, 55-76 then 6,
and 76+ then 7. I am not firmiliar with making formulas in
Excel at all and I have been having lots of trouble with
this.

 

[Paul Corrado]

Chris,

You can incorporate a VLOOKUP as follows:

Create a table with the LOWER end of each range in one column and the value
you wish to associate with that range in the next column as I have shown
below

0 1
10 2
21 3
31 4
45 5
55 6
76 7

Your formula would be

=VLOOKUP(sum("range of data to sum"),"range of your table",2,true)

HTH

PC

 

[Thomas]

Assume your six fields to sum are in B1 thru B6,change that as needed
=LOOKUP(SUM(B1:B6),{0,9.5,20.5,30.3,44.5,54.5,76},{1,2,3,4,5,6,7})

 

[Alain CROS]

Hi.

Your six field in A1:A6
You can try that but what happen if SUM(A1:A6)= 9.7 or 54.8 ?
=MATCH(SUM(A1:A6),{0;9.5;20.5;30.3;44.5;54.5;76},1)

Alain CROS.