Difficult Formula
-
01 Ağustos 2012 Çarşamba 21:27
Hey guys I'm trying to do something with Excel 2010 that I hope isn't outside the range of the functions. Although I'm afraid it might be since I'm not even really sure how to begin doing what I'm trying to achieve. Let me first explain what I'm trying to do, as I'm not sure if how I'm thinking about doing it is even the best way to go about it. I've created a data collection spreadsheet that has several total lines at the end. These total lines are all just SUM and COUNTIF formulas that will all result in a numerical total. What I want to do is create a formula that takes the total line number and scores it individually for each total line, then adds up the totals for a Grand Total Grade. For instance J3 is a total line that I am expecting the total to be between 0-2000 if the value is between 0-100 I would like 1 point assigned, 101-500=2pts and so forth. J6 is another total line where I expect the totals to be between 0-400 so I would like something like 0-50=1pt 51-100=2pts and so on. I would then like it to take the average of the points calculated and assign it a letter value. Something along the lines of =<1pt=A =<2pt=B =<3pt=C and so on. For instance J3=132,so 2pts and J6=20,so 1pt, Average score 1.5 points=B
I'm sure this post is not clear enough for a quick answer, I apologize it's the first time I've put what I want to do into words. I hope someone at least understands this enough to ask some questions so I can clear up any misunderstandings.
Thanks in Advance,
Adam
Tüm Yanıtlar
-
02 Ağustos 2012 Perşembe 01:00 On Wed, 1 Aug 2012 21:27:13 +0000, DefaOmega wrote:>>>Hey guys I'm trying to do something with Excel 2010 that I hope isn't outside the range of the functions. Although I'm afraid it might be since I'm not even really sure how to begin doing what I'm trying to achieve. Let me first explain what I'm trying to do, as I'm not sure if how I'm thinking about doing it is even the best way to go about it. I've created a data collection spreadsheet that has several total lines at the end. These total lines are all just SUM and COUNTIF formulas that will all result in a numerical total. What I want to do is create a formula that takes the total line number and scores it individually for each total line, then adds up the totals for a Grand Total Grade. For instance J3 is a total line that I am expecting the total to be between 0-2000 if the value is between 0-100 I would like 1 point assigned, 101-500=2pts and so forth. J6 is another total line where I expect the totals to be between 0-400 so I would like something like 0-50=1pt>51-100=2pts and so on. I would then like it to take the average of the points calculated and assign it a letter value. Something along the lines of =<1pt=A =<2pt=B =<3pt=C and so on. For instance J3=132,so 2pts and J6=20,so 1pt, Average score 1.5 points=B>>>>I'm sure this post is not clear enough for a quick answer, I apologize it's the first time I've put what I want to do into words. I hope someone at least understands this enough to ask some questions so I can clear up any misunderstandings.>>>>Thanks in Advance,>>AdamAll you need is a simple lookup table, or possibly several since you need to derive a different point number for scores in different columns.Look at VLOOKUP.
Ron- Yanıt Olarak İşaretleyen Jaynet ZhangMicrosoft Contingent Staff, Moderator 10 Ağustos 2012 Cuma 02:55
-
02 Ağustos 2012 Perşembe 09:32Moderatör
Hi,
I am agree with Ron's opinion. And here's a link about Vlookup function:
http://support.microsoft.com/kb/181213
Also, I think this can be down using a formula. Such as (supposed cell J3):
=IF(AND(J3>=0,J3<=100),"1 point",IF(AND(J3>=101,J3<=500),"2pts","3pts"))
Jaynet Zhang
TechNet Community Support
-
04 Ağustos 2012 Cumartesi 03:43
=IF(AND(J3>=0,J3<=100),"1 point",IF(AND(J3>=101,J3<=500),"2pts","3pts"))
may be revised as
=IF(J3>500,"3pts",IF(J3>=101,"2pts","1 point"))
- Yanıt Olarak İşaretleyen Jaynet ZhangMicrosoft Contingent Staff, Moderator 10 Ağustos 2012 Cuma 02:55
-
06 Ağustos 2012 Pazartesi 02:22Moderatör
Hi,
Jaynet Zhang
TechNet Community Support
.png)
