Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 31st 05, 07:13 PM
barb in NC
 
Posts: n/a
Default How to calculate a sum as one outcome of an IF statement

I am working on a grade book. The teacher gives 12 quizzes worth 10 points
each, but the maximum credit allowed for quizzes is 100 points, not the
mathematically possible 120 points. I think an IF statement is the answer,
my best try is below:

IF(=100, "100", "=L8+O8+R8+U8+X8+AA8+AD8+AG8+AJ8+AM8+AP8+AS8")

This worked on the true contdion, but the false condition is not returning
the sum, but rather =L8+O8+R8+U8+X8+AA8+AD8+AG8+AJ8+AM8+AP8+AS8.

I have tried all the variations I can think of to tell it to return the sum
in the false condition (the sum is less than 100), but I can't find the right
grammar. Removing the quotation marks made the statement invalid, as did
surrounding the L8+O8+R8+U8+X8+AA8+AD8+AG8+AJ8+AM8+AP8+AS8 with parentheses.

I tried to approach it with a SUMIF statement, but didn't see that as an
answer, though I admit I was running out of enthusiasm by then. I've tried
the online help, and as ever, not found the issue addressed. I'd sure
appreciate any suggestions.

THANKS!

  #2   Report Post  
Old March 31st 05, 07:25 PM
N Harkawat
 
Posts: n/a
Default

How about simply
=min(100,(L8+O8+R8+U8+X8+AA8+AD8+AG8+AJ8+AM8+AP8+A S8))

"barb in NC" wrote in message
...
I am working on a grade book. The teacher gives 12 quizzes worth 10 points
each, but the maximum credit allowed for quizzes is 100 points, not the
mathematically possible 120 points. I think an IF statement is the
answer,
my best try is below:

IF(=100, "100", "=L8+O8+R8+U8+X8+AA8+AD8+AG8+AJ8+AM8+AP8+AS8")

This worked on the true contdion, but the false condition is not returning
the sum, but rather =L8+O8+R8+U8+X8+AA8+AD8+AG8+AJ8+AM8+AP8+AS8.

I have tried all the variations I can think of to tell it to return the
sum
in the false condition (the sum is less than 100), but I can't find the
right
grammar. Removing the quotation marks made the statement invalid, as did
surrounding the L8+O8+R8+U8+X8+AA8+AD8+AG8+AJ8+AM8+AP8+AS8 with
parentheses.

I tried to approach it with a SUMIF statement, but didn't see that as an
answer, though I admit I was running out of enthusiasm by then. I've
tried
the online help, and as ever, not found the issue addressed. I'd sure
appreciate any suggestions.

THANKS!



  #3   Report Post  
Old March 31st 05, 08:01 PM
barb in NC
 
Posts: n/a
Default

thank you, thank you, thank you, thank you, THANK YOU! Worked like a charm.

"N Harkawat" wrote:

How about simply
=min(100,(L8+O8+R8+U8+X8+AA8+AD8+AG8+AJ8+AM8+AP8+A S8))

"barb in NC" wrote in message
...
I am working on a grade book. The teacher gives 12 quizzes worth 10 points
each, but the maximum credit allowed for quizzes is 100 points, not the
mathematically possible 120 points. I think an IF statement is the
answer,
my best try is below:

IF(=100, "100", "=L8+O8+R8+U8+X8+AA8+AD8+AG8+AJ8+AM8+AP8+AS8")

This worked on the true contdion, but the false condition is not returning
the sum, but rather =L8+O8+R8+U8+X8+AA8+AD8+AG8+AJ8+AM8+AP8+AS8.

I have tried all the variations I can think of to tell it to return the
sum
in the false condition (the sum is less than 100), but I can't find the
right
grammar. Removing the quotation marks made the statement invalid, as did
surrounding the L8+O8+R8+U8+X8+AA8+AD8+AG8+AJ8+AM8+AP8+AS8 with
parentheses.

I tried to approach it with a SUMIF statement, but didn't see that as an
answer, though I admit I was running out of enthusiasm by then. I've
tried
the online help, and as ever, not found the issue addressed. I'd sure
appreciate any suggestions.

THANKS!






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I calculate Vacation Time earned based on length of emplo. Kim Excel Discussion (Misc queries) 2 March 15th 05 08:04 PM
formula to calculate # of days between dates, excluding holidays abs2299 Excel Discussion (Misc queries) 8 March 3rd 05 02:21 AM
X IN A CELL TO CALCULATE A FORMULA JUSTIN Excel Worksheet Functions 1 January 3rd 05 11:08 PM
Not able to calculate. mark_kramarczyk Excel Worksheet Functions 1 December 29th 04 08:55 PM
How do you calculate the nth root of a number in Excel 2003? William Excel Worksheet Functions 2 November 17th 04 04:19 PM


All times are GMT +1. The time now is 03:35 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017