0

I need to the the minimum (or maximum, or some other values) of a range of data using Office Script in Excel. My data (a column of a table) contains only numeric values or empty cells.

I can loop over the data, that's not the problem, but I thought there must be an easier way as this is the very nature of a spreadsheet. However, it seems it is not so easy.

Something like WorksheetFunctions of course doesn't exists.

I read the data into a variable, that works

 let myValues = table.getColumnByName('myColumn').getRange().getValues();

But what now?

  • Using Math.min(myValues) fails because myValues contains data of that fancy string | number | boolean data type.
  • Tried to declare myValues with as number. The editor gives a nasty hint that this may fail, but checking with the debugger (haha, just kidding) with console.log shows that the content are numbers. But Math.min(myValues) still fails, even if I type a 0 in all empty cells.
  • Thought that maybe the strange array definition causes this and tried to use the flat() function, but that is not avaialable for the array ("does not exist on type...")

Even Copilot gives up and uses a loop. Is there really no better way?

Improve this question
5
  • 1
    Insert a formula into a cell and then read the result maybe? Commented Jun 12 at 11:02
  • Yeah, thought about that, but that is really ugly. Commented Jun 12 at 11:15
  • It works though AND it uses the built in power of Excel. Commented Jun 12 at 23:09
  • Why doesn't the WorksheetFunctions work? Set myRange = Worksheets("Sheet1").Range("A1:A5"); answer = Application.WorksheetFunction.Min(myRange); MsgBox answer Commented Jun 26 at 14:20
  • That's VBA. I am talking about Office Script... Commented Jun 26 at 14:44
Related questions
276
Return empty cell from formula in Excel
0
Use a span of Cells in getCell?
0
Selecting a variable size range of non empty cells in a fixed bigger range using only vanilla excel function (without vba)
Load 6 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.