Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding closest value within an array
I've got some results which I need to grade against some
benchmarks e.g. A=90% and above B=80% and above C=70% and above I've got 10000 pupils with varying results is there any macro I can create to give each pupil a grade for their result and put it on a worksheet (I would use formulas but that makes the workbook way too big - as I have to repeat this for 18 subjects!) Thank you for any help you can give. Stewart |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding closest value within an array
Hi Stewart,
I would use formulas but that makes the workbook way too big - as I have to repeat this for 18 subjects! Use your formulas and then copy / paste special / values to convert the formulas to values. If you still want a macro solution, please give details of the data layout. --- Regards, Norman "Stewart" wrote in message ... I've got some results which I need to grade against some benchmarks e.g. A=90% and above B=80% and above C=70% and above I've got 10000 pupils with varying results is there any macro I can create to give each pupil a grade for their result and put it on a worksheet (I would use formulas but that makes the workbook way too big - as I have to repeat this for 18 subjects!) Thank you for any help you can give. Stewart |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding closest value within an array
A=90% and above B=80% and above C=70% and above I've got 10000 pupils with varying results is there any macro I can create to give each pupil a grade for their result and put it on a worksheet (I would use formulas but range("A65536").select ' this bit finds the last row which contains data activecell.end(xlUp).select lastrow = activecell.row with sheets("Sheet1") 'or whatever your sheet is called for r = 1 to lastrow mark = .cells( r, 2).value 'looking for mark in column 2 (i.e. B) if mark = 90 then grade = "A" elseif mark = 80 then grade = "B" elseif mark = 70 then grade = "C" else grade = "D" endif .cells( r, 3).value = grade 'write value into column 3 (i.e. C) next end with Iain King |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding closest value within an array
The thing is that I want to have static data tables then a
macro to work out grades when called upon, not calculate them then display them. The reason for this is that these are provisional results and are likely to be a state of flux, so if I can just dump the data in then run a macro that is the better solution. Is there anyway of creating an array then looking up the value against the array in a similar way that the match function works? e.g. the array would be (90,80,70) the value to look up would be 76 and you'd want it to return 70 as it is above 70 but less than 80. Stewart |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding closest value within an array
Easy when you know how - cheers, that's given me an idea
on how to do the other part of the problem I've got too - nice one. Stewart -----Original Message----- A=90% and above B=80% and above C=70% and above I've got 10000 pupils with varying results is there any macro I can create to give each pupil a grade for their result and put it on a worksheet (I would use formulas but range("A65536").select ' this bit finds the last row which contains data activecell.end(xlUp).select lastrow = activecell.row with sheets("Sheet1") 'or whatever your sheet is called for r = 1 to lastrow mark = .cells( r, 2).value 'looking for mark in column 2 (i.e. B) if mark = 90 then grade = "A" elseif mark = 80 then grade = "B" elseif mark = 70 then grade = "C" else grade = "D" endif .cells( r, 3).value = grade 'write value into column 3 (i.e. C) next end with Iain King . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Let user insert value that highlights closest value in an array. | Excel Discussion (Misc queries) | |||
Array Lookup to Find Closest Date and Next Closest Date | Excel Worksheet Functions | |||
finding a date/time in a list that is closest to an existing date/ | Excel Discussion (Misc queries) | |||
Finding Closest Match | Excel Worksheet Functions | |||
Match Closest Results from Data Array | Excel Discussion (Misc queries) |