Correct Ranking Results but output different to actual results

[MitchBVI]

That heading is not a good description I am afraid . I am trying to rank a series of numbers into one of 4 groups (1,2,3,4) on an Excel worksheet. The routine below enters into each cell in column “B” what ever the 1st calculation returns. The log command in the third last line returns the correct results but the instruction in the 2nd last line puts as I said the result for the 1st cell in all the others. I have struggled with this and just cannot figure where I am going wrong.

Thanks

set VolatilityRankData to {}
set VolatilityData to {}
set RankList to {1, 2, 3, 4}
tell application “Microsoft Excel”
set LastRow to word 4 of (get address of last cell of used range of active sheet)
set StocksExist to the string value of cell (“A2:A” & LastRow) --of last sheet of active workbook
repeat with x from 1 to LastRow - 1
set end of VolatilityData to (item x of StocksExist as number)
end repeat
set max to some item in VolatilityData
copy max to min
repeat with x in VolatilityData
if x > max then set max to x’s contents
if x < min then set min to x’s contents
end repeat
log min & " " & max
set Avg to (max + min) / 2
set minmid to (Avg + min) / 2
set maxmid to (Avg + max) / 2
repeat with RowCnt from 1 to LastRow - 1
set CheckAdd to item RowCnt of VolatilityData as number
if CheckAdd < minmid then
set end of VolatilityRankData to item 1 of RankList
else if CheckAdd > minmid and CheckAdd ≤ Avg then
set end of VolatilityRankData to item 2 of RankList
else if CheckAdd > Avg and CheckAdd ≤ maxmid then
set end of VolatilityRankData to item 3 of RankList
else
set end of VolatilityRankData to item 4 of RankList
end if
set NewStocksRank to VolatilityRankData
log item RowCnt of VolatilityRankData
end repeat
log VolatilityRankData
set value of range (“B2:B” & LastRow) of active sheet to VolatilityRankData
end tell

these are the number s in my Excel worksheet

Results
0.300485906
0.230823511
0.230823446
0.252557664
0.235610249
0.452173976
0.314753399
0.298899209
0.239691334
0.367018115
0.458121626
0.362480715
0.252220942
0.334963319
0.372350426
0.271136852
0.36608831
0.244227318
0.364477192
0.485768222
0.375542007
0.447312698
0.294882411
0.29418524
0.252731059

[Mockman]

Try adding as list after each of the four VolatilityRankData lines, like so:

set end of VolatilityRankData to item 1 of RankList as list

Essentially, you have to add the values as a list of lists

set value of range "B2:B26" of active sheet to {{2}, {1}, {1}, {1}, {1}, {4}, {2}, {2}, {1}, {3}, {4}, {3}, {1}, {2}, {3}, {1}, {3}, {1}, {3}, {4}, {3}, {4}, {2}, {1}, {1}}

What you were doing was adding as a single list

set formula of range "B2:B26" of active sheet to {2, 1, 1, 1, 1, 4, 2, 2, 1, 3, 4, 3, 1, 2, 3, 1, 3, 1, 3, 4, 3, 4, 2, 1, 1}

Apparently, it will use only the first item when you take this approach.

@roosterboy alluded to it in this post a while ago — at least, I think it’s basically the same thing:

Excel Get Maximum Value in a Row

This is because of the way Excel represents cells in AppleScript. A group of cells is represented as a nested list of rows and columns, such that {{R1C1, R1C2}, {R2C1, R2C2}, {R3C1, R2C2}, etc…} So a single row of multiple columns comes out like this: {{R1C1, R1C2, R1C3, etc…}} And a single column of multiple rows like this: {{R1C1}, {R2C1}, {R3C1}, etc…} You could loop through the nested lists, but an easier way to do it is just to use Excel’s built-in formula: tell application "Microsoft E…

[MitchBVI]

Thank you once again, I had missed the solution provided before but I was looking for the quickest way as that example list in practice could be very large.

[Mockman]

I think that I’d read that thread when it was written and then forgot about it. However it popped up in the ‘suggested topics’ list at the bottom of the some post I had open and I took a look at it and tried it out with your script/data and it worked.