I'm sure the answer is easy, but I can't figure it out. I'm trying to sum
one column up to a preset threshold (like 60) and then want to sum up
corresponding entries, but I only want the entries that coresspond to the
entries that don't exceed 60.

So far I've create a GPA calculator where you place credit hours in one
column, a letter grade in another, and then it uses a vlookup function to
find out how many points that letter grade is worth, and then uses sum
functions to sum up the points and the credit hours. It then divides the two
to get a GPA.

I'm stuck, however, in trying to figure out how to get it to do a "Last 60
hours GPA" where it will sum the credit hours up to 60, and then sum up the
corresponding grade points. I can figure it out by hand very easily, so I
figure there must be a simple automated way to do it, as well, but the best
I've come up with is to create a really huge nested IF statement where I say
"IF(cell1+cell2<60,IF(sum(cell1:cell2)+cell3<60,IF (sum(cell1:cell3)+cell4<60,
etc" and then just back out the IF statements with sum functions, but that's
just insane and it won't tell me which ones have been accepted for summing up
the grade points that correspond to the credit hours. Using this method I'd
then have to create another huge nested IF statement where IF the credit
hours, but have a sum of the grade points on teh back side of the function.
Anyone know of an easier way? I'd rather avoid anything that involves Visual
Basic. Thx in advance.

Hi

i think SUMIF will give you what you want
=SUMIF(A1:A100,"<="&60,B1:B100)
where you're checking the range A1:A100 if it is less than or equal to 60
and summing the corresponding values in the range B1:B100

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Stuck Student" <Stuck wrote in message
...
I'm sure the answer is easy, but I can't figure it out. I'm trying to sum
one column up to a preset threshold (like 60) and then want to sum up
corresponding entries, but I only want the entries that coresspond to the
entries that don't exceed 60.

So far I've create a GPA calculator where you place credit hours in one
column, a letter grade in another, and then it uses a vlookup function to
find out how many points that letter grade is worth, and then uses sum
functions to sum up the points and the credit hours. It then divides the
two
to get a GPA.

I'm stuck, however, in trying to figure out how to get it to do a "Last 60
hours GPA" where it will sum the credit hours up to 60, and then sum up
the
corresponding grade points. I can figure it out by hand very easily, so I
figure there must be a simple automated way to do it, as well, but the
best
I've come up with is to create a really huge nested IF statement where I
say
"IF(cell1+cell2<60,IF(sum(cell1:cell2)+cell3<60,IF (sum(cell1:cell3)+cell4<60,
etc" and then just back out the IF statements with sum functions, but
that's
just insane and it won't tell me which ones have been accepted for summing
up
the grade points that correspond to the credit hours. Using this method
I'd
then have to create another huge nested IF statement where IF the credit
hours, but have a sum of the grade points on teh back side of the
function.
Anyone know of an easier way? I'd rather avoid anything that involves
Visual
Basic. Thx in advance.

The SUMIF function doesn't work because as I have it set up now I list my
classes, class by class and it will simply sum everything because everything
is less than 60. For example, where first column is name, second is credit
hours, third is grade, fourth is weighted grade points:

ManEc 3 A 12
Biol 3 C 6
Acc 3 B 9
etc.

If I say SUMIF(Column2,<=60,Column2) it will sum up everything. I only want
the first X number of entries until some running total for column2 hits 60
and then don't want to sum up anything after that. So I don't think SUMIF
will work, unless there is something about it I don't understand. Thx again.


"JulieD" wrote:

Hi

i think SUMIF will give you what you want
=SUMIF(A1:A100,"<="&60,B1:B100)
where you're checking the range A1:A100 if it is less than or equal to 60
and summing the corresponding values in the range B1:B100

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Stuck Student" <Stuck wrote in message
...
I'm sure the answer is easy, but I can't figure it out. I'm trying to sum
one column up to a preset threshold (like 60) and then want to sum up
corresponding entries, but I only want the entries that coresspond to the
entries that don't exceed 60.

So far I've create a GPA calculator where you place credit hours in one
column, a letter grade in another, and then it uses a vlookup function to
find out how many points that letter grade is worth, and then uses sum
functions to sum up the points and the credit hours. It then divides the
two
to get a GPA.

I'm stuck, however, in trying to figure out how to get it to do a "Last 60
hours GPA" where it will sum the credit hours up to 60, and then sum up
the
corresponding grade points. I can figure it out by hand very easily, so I
figure there must be a simple automated way to do it, as well, but the
best
I've come up with is to create a really huge nested IF statement where I
say
"IF(cell1+cell2<60,IF(sum(cell1:cell2)+cell3<60,IF (sum(cell1:cell3)+cell4<60,
etc" and then just back out the IF statements with sum functions, but
that's
just insane and it won't tell me which ones have been accepted for summing
up
the grade points that correspond to the credit hours. Using this method
I'd
then have to create another huge nested IF statement where IF the credit
hours, but have a sum of the grade points on teh back side of the
function.
Anyone know of an easier way? I'd rather avoid anything that involves
Visual
Basic. Thx in advance.

Okay

in E2 how about
=IF(SUM($B$2:B2)<=60,SUM($B$2:B2),"")
and fill down the column

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Stuck Student" <Stuck wrote in message
...
The SUMIF function doesn't work because as I have it set up now I list my
classes, class by class and it will simply sum everything because
everything
is less than 60. For example, where first column is name, second is
credit
hours, third is grade, fourth is weighted grade points:

ManEc 3 A 12
Biol 3 C 6
Acc 3 B 9
etc.

If I say SUMIF(Column2,<=60,Column2) it will sum up everything. I only
want
the first X number of entries until some running total for column2 hits 60
and then don't want to sum up anything after that. So I don't think SUMIF
will work, unless there is something about it I don't understand. Thx
again.


"JulieD" wrote:

Hi

i think SUMIF will give you what you want
=SUMIF(A1:A100,"<="&60,B1:B100)
where you're checking the range A1:A100 if it is less than or equal to 60
and summing the corresponding values in the range B1:B100

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Stuck Student" <Stuck wrote in
message
...
I'm sure the answer is easy, but I can't figure it out. I'm trying to
sum
one column up to a preset threshold (like 60) and then want to sum up
corresponding entries, but I only want the entries that coresspond to
the
entries that don't exceed 60.

So far I've create a GPA calculator where you place credit hours in one
column, a letter grade in another, and then it uses a vlookup function
to
find out how many points that letter grade is worth, and then uses sum
functions to sum up the points and the credit hours. It then divides
the
two
to get a GPA.

I'm stuck, however, in trying to figure out how to get it to do a "Last
60
hours GPA" where it will sum the credit hours up to 60, and then sum up
the
corresponding grade points. I can figure it out by hand very easily,
so I
figure there must be a simple automated way to do it, as well, but the
best
I've come up with is to create a really huge nested IF statement where
I
say
"IF(cell1+cell2<60,IF(sum(cell1:cell2)+cell3<60,IF (sum(cell1:cell3)+cell4<60,
etc" and then just back out the IF statements with sum functions, but
that's
just insane and it won't tell me which ones have been accepted for
summing
up
the grade points that correspond to the credit hours. Using this
method
I'd
then have to create another huge nested IF statement where IF the
credit
hours, but have a sum of the grade points on teh back side of the
function.
Anyone know of an easier way? I'd rather avoid anything that involves
Visual
Basic. Thx in advance.

Hi!

Not sure what you're trying to sum as the final result but it sounds like
you need a helper column to identify where 60 is.

Assume you need a progressive sum of column 2 (B?):

=SUM(B$1:B1)

Copy down as needed.

Now you'll know where 60 is!

Biff

"Stuck Student" <Stuck wrote in message
...
The SUMIF function doesn't work because as I have it set up now I list my
classes, class by class and it will simply sum everything because
everything
is less than 60. For example, where first column is name, second is
credit
hours, third is grade, fourth is weighted grade points:

ManEc 3 A 12
Biol 3 C 6
Acc 3 B 9
etc.

If I say SUMIF(Column2,<=60,Column2) it will sum up everything. I only
want
the first X number of entries until some running total for column2 hits 60
and then don't want to sum up anything after that. So I don't think SUMIF
will work, unless there is something about it I don't understand. Thx
again.


"JulieD" wrote:

Hi

i think SUMIF will give you what you want
=SUMIF(A1:A100,"<="&60,B1:B100)
where you're checking the range A1:A100 if it is less than or equal to 60
and summing the corresponding values in the range B1:B100

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Stuck Student" <Stuck wrote in
message
...
I'm sure the answer is easy, but I can't figure it out. I'm trying to
sum
one column up to a preset threshold (like 60) and then want to sum up
corresponding entries, but I only want the entries that coresspond to
the
entries that don't exceed 60.

So far I've create a GPA calculator where you place credit hours in one
column, a letter grade in another, and then it uses a vlookup function
to
find out how many points that letter grade is worth, and then uses sum
functions to sum up the points and the credit hours. It then divides
the
two
to get a GPA.

I'm stuck, however, in trying to figure out how to get it to do a "Last
60
hours GPA" where it will sum the credit hours up to 60, and then sum up
the
corresponding grade points. I can figure it out by hand very easily,
so I
figure there must be a simple automated way to do it, as well, but the
best
I've come up with is to create a really huge nested IF statement where
I
say
"IF(cell1+cell2<60,IF(sum(cell1:cell2)+cell3<60,IF (sum(cell1:cell3)+cell4<60,
etc" and then just back out the IF statements with sum functions, but
that's
just insane and it won't tell me which ones have been accepted for
summing
up
the grade points that correspond to the credit hours. Using this
method
I'd
then have to create another huge nested IF statement where IF the
credit
hours, but have a sum of the grade points on teh back side of the
function.
Anyone know of an easier way? I'd rather avoid anything that involves
Visual
Basic. Thx in advance.