| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF statements terribly frustrated!
I have a question, I am trying to work on a grade book spread sheet and I am
having no luck. If a number is more than 13 in a box then it should ad 1 to each number over. If a nubme is less than 13 then there is a 2 point deduction for each number. I think I need to use a if then statement right? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF statements terribly frustrated!
I'm not really clear what you're trying to calculate but I think you'll need
something like this: Assuming the value you are testing is in cell A1: =IF(A113,A1-13+1,(A1-13)*2) this will give 0 if the number in the box is equal to 13 (you don't say what should happen in that situation). You may need to adjust the (A1-13)*2 part of the equation if you're actually taking the 2 points away from the <13 points already there, it's not clear exactly what results you are expecting. A Jones "Mandy" wrote: I have a question, I am trying to work on a grade book spread sheet and I am having no luck. If a number is more than 13 in a box then it should ad 1 to each number over. If a nubme is less than 13 then there is a 2 point deduction for each number. I think I need to use a if then statement right? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF statements terribly frustrated!
Let's see if I understand you correctly.....
With A1: 5 B1: 10 C1: 13 D1: 15 The sum is 43, but Number 15 is greater than 13...so add 1 Number 5 and 10 are less than 13....so subtract 2*2 (4) Final total = 43+1-4=40 If that's correct, than E1: =SUM(A1:D1)+COUNTIF(A1:D1,"13")-COUNTIF(A1:D1,"<13")*2 Does that help? *********** Regards, Ron XL2002, WinXP "Mandy" wrote: I have a question, I am trying to work on a grade book spread sheet and I am having no luck. If a number is more than 13 in a box then it should ad 1 to each number over. If a nubme is less than 13 then there is a 2 point deduction for each number. I think I need to use a if then statement right? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF statements terribly frustrated!
wOW HOW DO YOU DO THAT! RIGH ON THE MONEY
"Andrea Jones" wrote: I'm not really clear what you're trying to calculate but I think you'll need something like this: Assuming the value you are testing is in cell A1: =IF(A113,A1-13+1,(A1-13)*2) this will give 0 if the number in the box is equal to 13 (you don't say what should happen in that situation). You may need to adjust the (A1-13)*2 part of the equation if you're actually taking the 2 points away from the <13 points already there, it's not clear exactly what results you are expecting. A Jones "Mandy" wrote: I have a question, I am trying to work on a grade book spread sheet and I am having no luck. If a number is more than 13 in a box then it should ad 1 to each number over. If a nubme is less than 13 then there is a 2 point deduction for each number. I think I need to use a if then statement right? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF statements terribly frustrated!
I have to assign the below letter in the grade column, is that the hlookup?
Name Semester Average Grade Bragg, Cheryl 57.0 Goodman, Neil 78.0 Hathaway, Craig 85.0 Mason, Alan 88.3 Nelson, Rick 96.0 Ohara, John 66.0 Rogers, Lorraine 96.0 Simpson, Jake 54.0 Slattery, Joan 83.0 Street, Brian 93.0 0 60 70 78 84 87 90 93 98 F D C B- B B+ A- A A+ "Andrea Jones" wrote: I'm not really clear what you're trying to calculate but I think you'll need something like this: Assuming the value you are testing is in cell A1: =IF(A113,A1-13+1,(A1-13)*2) this will give 0 if the number in the box is equal to 13 (you don't say what should happen in that situation). You may need to adjust the (A1-13)*2 part of the equation if you're actually taking the 2 points away from the <13 points already there, it's not clear exactly what results you are expecting. A Jones "Mandy" wrote: I have a question, I am trying to work on a grade book spread sheet and I am having no luck. If a number is more than 13 in a box then it should ad 1 to each number over. If a nubme is less than 13 then there is a 2 point deduction for each number. I think I need to use a if then statement right? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF statements terribly frustrated!
It is kinda confusing to me
"Ron Coderre" wrote: Let's see if I understand you correctly..... With A1: 5 B1: 10 C1: 13 D1: 15 The sum is 43, but Number 15 is greater than 13...so add 1 Number 5 and 10 are less than 13....so subtract 2*2 (4) Final total = 43+1-4=40 If that's correct, than E1: =SUM(A1:D1)+COUNTIF(A1:D1,"13")-COUNTIF(A1:D1,"<13")*2 Does that help? *********** Regards, Ron XL2002, WinXP "Mandy" wrote: I have a question, I am trying to work on a grade book spread sheet and I am having no luck. If a number is more than 13 in a box then it should ad 1 to each number over. If a nubme is less than 13 then there is a 2 point deduction for each number. I think I need to use a if then statement right? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF statements terribly frustrated!
It is kinda confusing to me<<
That's only because I was WAAAYYYY off the mark in tying to decipher what you wanted. ------------------------ Let's see if I can redeem myself with your second question..... with your data list in cells A1:C11 (Names in Col_A, Avg in Col_B, Grade in Col_C) and your grade table in cells A20:I21 Bragg's grade in C2 would be C1: =HLOOKUP(B2,$A$20:$I$21,2,1) Copy that cell down through C11 Does that help? *********** Regards, Ron XL2002, WinXP "Mandy" wrote: It is kinda confusing to me "Ron Coderre" wrote: Let's see if I understand you correctly..... With A1: 5 B1: 10 C1: 13 D1: 15 The sum is 43, but Number 15 is greater than 13...so add 1 Number 5 and 10 are less than 13....so subtract 2*2 (4) Final total = 43+1-4=40 If that's correct, than E1: =SUM(A1:D1)+COUNTIF(A1:D1,"13")-COUNTIF(A1:D1,"<13")*2 Does that help? *********** Regards, Ron XL2002, WinXP "Mandy" wrote: I have a question, I am trying to work on a grade book spread sheet and I am having no luck. If a number is more than 13 in a box then it should ad 1 to each number over. If a nubme is less than 13 then there is a 2 point deduction for each number. I think I need to use a if then statement right? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF statements terribly frustrated!
Hopefully, you noticed my typo.
The formula was right, but it goes in C2, not C1. This... C1: =HLOOKUP(B2,$A$20:$I$21,2,1) Should have been... C2: =HLOOKUP(B2,$A$20:$I$21,2,1) Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: It is kinda confusing to me<< That's only because I was WAAAYYYY off the mark in tying to decipher what you wanted. ------------------------ Let's see if I can redeem myself with your second question..... with your data list in cells A1:C11 (Names in Col_A, Avg in Col_B, Grade in Col_C) and your grade table in cells A20:I21 Bragg's grade in C2 would be C1: =HLOOKUP(B2,$A$20:$I$21,2,1) Copy that cell down through C11 Does that help? *********** Regards, Ron XL2002, WinXP "Mandy" wrote: It is kinda confusing to me "Ron Coderre" wrote: Let's see if I understand you correctly..... With A1: 5 B1: 10 C1: 13 D1: 15 The sum is 43, but Number 15 is greater than 13...so add 1 Number 5 and 10 are less than 13....so subtract 2*2 (4) Final total = 43+1-4=40 If that's correct, than E1: =SUM(A1:D1)+COUNTIF(A1:D1,"13")-COUNTIF(A1:D1,"<13")*2 Does that help? *********** Regards, Ron XL2002, WinXP "Mandy" wrote: I have a question, I am trying to work on a grade book spread sheet and I am having no luck. If a number is more than 13 in a box then it should ad 1 to each number over. If a nubme is less than 13 then there is a 2 point deduction for each number. I think I need to use a if then statement right? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF statements terribly frustrated!
Ok, I got it to do this and it comes back with the correct but it drags F's
down thw whole column, now what?// "Ron Coderre" wrote: Hopefully, you noticed my typo. The formula was right, but it goes in C2, not C1. This... C1: =HLOOKUP(B2,$A$20:$I$21,2,1) Should have been... C2: =HLOOKUP(B2,$A$20:$I$21,2,1) Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: It is kinda confusing to me<< That's only because I was WAAAYYYY off the mark in tying to decipher what you wanted. ------------------------ Let's see if I can redeem myself with your second question..... with your data list in cells A1:C11 (Names in Col_A, Avg in Col_B, Grade in Col_C) and your grade table in cells A20:I21 Bragg's grade in C2 would be C1: =HLOOKUP(B2,$A$20:$I$21,2,1) Copy that cell down through C11 Does that help? *********** Regards, Ron XL2002, WinXP "Mandy" wrote: It is kinda confusing to me "Ron Coderre" wrote: Let's see if I understand you correctly..... With A1: 5 B1: 10 C1: 13 D1: 15 The sum is 43, but Number 15 is greater than 13...so add 1 Number 5 and 10 are less than 13....so subtract 2*2 (4) Final total = 43+1-4=40 If that's correct, than E1: =SUM(A1:D1)+COUNTIF(A1:D1,"13")-COUNTIF(A1:D1,"<13")*2 Does that help? *********** Regards, Ron XL2002, WinXP "Mandy" wrote: I have a question, I am trying to work on a grade book spread sheet and I am having no luck. If a number is more than 13 in a box then it should ad 1 to each number over. If a nubme is less than 13 then there is a 2 point deduction for each number. I think I need to use a if then statement right? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF statements terribly frustrated!
When you dragged the formula in C2 down through C11,
the formula in C3 should have changed to =HLOOKUP(B3,$A$20:$I$21,2,1) Note that the ref to B2 changed to B3. If that's the case yet the same grade as C2 displays, then it sounds like you have Calculation set to manual. Try this: From the Excel main menu: <tools<options<calculation tab Check: Automatic. Does that help? *********** Regards, Ron XL2002, WinXP "Mandy" wrote: Ok, I got it to do this and it comes back with the correct but it drags F's down thw whole column, now what?// "Ron Coderre" wrote: Hopefully, you noticed my typo. The formula was right, but it goes in C2, not C1. This... C1: =HLOOKUP(B2,$A$20:$I$21,2,1) Should have been... C2: =HLOOKUP(B2,$A$20:$I$21,2,1) Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: It is kinda confusing to me<< That's only because I was WAAAYYYY off the mark in tying to decipher what you wanted. ------------------------ Let's see if I can redeem myself with your second question..... with your data list in cells A1:C11 (Names in Col_A, Avg in Col_B, Grade in Col_C) and your grade table in cells A20:I21 Bragg's grade in C2 would be C1: =HLOOKUP(B2,$A$20:$I$21,2,1) Copy that cell down through C11 Does that help? *********** Regards, Ron XL2002, WinXP "Mandy" wrote: It is kinda confusing to me "Ron Coderre" wrote: Let's see if I understand you correctly..... With A1: 5 B1: 10 C1: 13 D1: 15 The sum is 43, but Number 15 is greater than 13...so add 1 Number 5 and 10 are less than 13....so subtract 2*2 (4) Final total = 43+1-4=40 If that's correct, than E1: =SUM(A1:D1)+COUNTIF(A1:D1,"13")-COUNTIF(A1:D1,"<13")*2 Does that help? *********** Regards, Ron XL2002, WinXP "Mandy" wrote: I have a question, I am trying to work on a grade book spread sheet and I am having no luck. If a number is more than 13 in a box then it should ad 1 to each number over. If a nubme is less than 13 then there is a 2 point deduction for each number. I think I need to use a if then statement right? |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF statements terribly frustrated!
It is actualy this:
=HLOOKUP(I4,$B$15:$B$16,2,1) but it won't copy down, it is set on automatic "Ron Coderre" wrote: When you dragged the formula in C2 down through C11, the formula in C3 should have changed to =HLOOKUP(B3,$A$20:$I$21,2,1) Note that the ref to B2 changed to B3. If that's the case yet the same grade as C2 displays, then it sounds like you have Calculation set to manual. Try this: From the Excel main menu: <tools<options<calculation tab Check: Automatic. Does that help? *********** Regards, Ron XL2002, WinXP "Mandy" wrote: Ok, I got it to do this and it comes back with the correct but it drags F's down thw whole column, now what?// "Ron Coderre" wrote: Hopefully, you noticed my typo. The formula was right, but it goes in C2, not C1. This... C1: =HLOOKUP(B2,$A$20:$I$21,2,1) Should have been... C2: =HLOOKUP(B2,$A$20:$I$21,2,1) Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: It is kinda confusing to me<< That's only because I was WAAAYYYY off the mark in tying to decipher what you wanted. ------------------------ Let's see if I can redeem myself with your second question..... with your data list in cells A1:C11 (Names in Col_A, Avg in Col_B, Grade in Col_C) and your grade table in cells A20:I21 Bragg's grade in C2 would be C1: =HLOOKUP(B2,$A$20:$I$21,2,1) Copy that cell down through C11 Does that help? *********** Regards, Ron XL2002, WinXP "Mandy" wrote: It is kinda confusing to me "Ron Coderre" wrote: Let's see if I understand you correctly..... With A1: 5 B1: 10 C1: 13 D1: 15 The sum is 43, but Number 15 is greater than 13...so add 1 Number 5 and 10 are less than 13....so subtract 2*2 (4) Final total = 43+1-4=40 If that's correct, than E1: =SUM(A1:D1)+COUNTIF(A1:D1,"13")-COUNTIF(A1:D1,"<13")*2 Does that help? *********** Regards, Ron XL2002, WinXP "Mandy" wrote: I have a question, I am trying to work on a grade book spread sheet and I am having no luck. If a number is more than 13 in a box then it should ad 1 to each number over. If a nubme is less than 13 then there is a 2 point deduction for each number. I think I need to use a if then statement right? |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF statements terribly frustrated!
I just noticed what I did, I forgot to change to teh $I.
Thank you so very much for all of your help! "Ron Coderre" wrote: When you dragged the formula in C2 down through C11, the formula in C3 should have changed to =HLOOKUP(B3,$A$20:$I$21,2,1) Note that the ref to B2 changed to B3. If that's the case yet the same grade as C2 displays, then it sounds like you have Calculation set to manual. Try this: From the Excel main menu: <tools<options<calculation tab Check: Automatic. Does that help? *********** Regards, Ron XL2002, WinXP "Mandy" wrote: Ok, I got it to do this and it comes back with the correct but it drags F's down thw whole column, now what?// "Ron Coderre" wrote: Hopefully, you noticed my typo. The formula was right, but it goes in C2, not C1. This... C1: =HLOOKUP(B2,$A$20:$I$21,2,1) Should have been... C2: =HLOOKUP(B2,$A$20:$I$21,2,1) Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: It is kinda confusing to me<< That's only because I was WAAAYYYY off the mark in tying to decipher what you wanted. ------------------------ Let's see if I can redeem myself with your second question..... with your data list in cells A1:C11 (Names in Col_A, Avg in Col_B, Grade in Col_C) and your grade table in cells A20:I21 Bragg's grade in C2 would be C1: =HLOOKUP(B2,$A$20:$I$21,2,1) Copy that cell down through C11 Does that help? *********** Regards, Ron XL2002, WinXP "Mandy" wrote: It is kinda confusing to me "Ron Coderre" wrote: Let's see if I understand you correctly..... With A1: 5 B1: 10 C1: 13 D1: 15 The sum is 43, but Number 15 is greater than 13...so add 1 Number 5 and 10 are less than 13....so subtract 2*2 (4) Final total = 43+1-4=40 If that's correct, than E1: =SUM(A1:D1)+COUNTIF(A1:D1,"13")-COUNTIF(A1:D1,"<13")*2 Does that help? *********** Regards, Ron XL2002, WinXP "Mandy" wrote: I have a question, I am trying to work on a grade book spread sheet and I am having no luck. If a number is more than 13 in a box then it should ad 1 to each number over. If a nubme is less than 13 then there is a 2 point deduction for each number. I think I need to use a if then statement right? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 2 IF Statements not mutually exclusive | Excel Discussion (Misc queries) | |||
| UDFunctions and nested If-the-else statements | Excel Worksheet Functions | |||
| Linking two IF statements together | Excel Discussion (Misc queries) | |||
| Nested IF statements | Excel Worksheet Functions | |||
| If statements | Excel Worksheet Functions | |||
Linear Mode
