Getting an error on a formula for Excel Mac which works on Excel windows

[SMit07]

Hi all,

I have a formula on my excel sheet which works perfectly on the windows machine I made the sheet on which returns the most common value/text within a column.

The formula looks like this, say my array is in column 2 of Table 1, =INDEX(Table_1[[#All],[Column2]],MODE(MATCH(Table_1[[#All],[Column2]],Table_1[[#All],[Column2]],0)))

Like I said, the formula returns the correct value on the windows machine I made the sheet on, but when I edit the sheet on my mac, it provides an error.

I've tried sending it to other windows machines to test, and they're getting the same error. Would it possibly be a setting I've changed a while ago?
I need to submit this spreadsheet in less then 48 hours so any help would be greatly appreciated!

Thanks!

 

[6StringJazzer]

What is the error? What version of Excel is on the Mac and the other Windows machines? (It doesn't look to me like you are doing anything that would work in 365 but fail in older versions, unless they are really old.)

Can you share the data here so we can try to reproduce this?

Have you tried stepping through the formula using Evaluate to see where it fails?

 

[SMit07]

What is the error? What version of Excel is on the Mac and the other Windows machines? (It doesn't look to me like you are doing anything that would work in 365 but fail in older versions, unless they are really old.)

Can you share the data here so we can try to reproduce this?

Have you tried stepping through the formula using Evaluate to see where it fails?

Hi Jeff,

Thanks for taking the time to reply!

I've attached screenshots on my mac of the version, formula which has been split up to show the errors down to the core and an example of the portion of Table 1 being analysed.

Note I've got some conditional statements there to return the most frequent on the chosen date (Cell G10) and to ignore blank cells.

The below is from my windows pc where the spreadsheet was created and working.

The formula in total reads
=INDEX(Table_1[[#All],[Column2]],MODE(IF(Table_1[[#All],[Column2]]<>"",IF(Table_1[[#All],[Column1]]=$G$10,MATCH(Table_1[[#All],[Column2]],Table_1[[#All],[Column2]],0),""))))

Which returns the value "RealBookie" when G10 = 7/07/2022.
I have a 365 subscription on my Windows pc.

Sorry if this is a bit confusing.

 

[SMit07]

Hi Jeff,

Thanks for taking the time to reply!

I've attached screenshots on my mac of the version, formula which has been split up to show the errors down to the core and an example of the portion of Table 1 being analysed.

Note I've got some conditional statements there to return the most frequent on the chosen date (Cell G10) and to ignore blank cells.

View attachment 68854View attachment 68857

View attachment 68856


The below is from my windows pc where the spreadsheet was created and working.

The formula in total reads
=INDEX(Table_1[[#All],[Column2]],MODE(IF(Table_1[[#All],[Column2]]<>"",IF(Table_1[[#All],[Column1]]=$G$10,MATCH(Table_1[[#All],[Column2]],Table_1[[#All],[Column2]],0),""))))

Which returns the value "RealBookie" when G10 = 7/07/2022.
I have a 365 subscription on my Windows pc.

Sorry if this is a bit confusing.

Hmm, I just did some testing and maybe it's an issue with the formula.
I've replicated the data onto a blank sheet and am getting more errors, I've attached another screenshot here.

The formula should give the result 'BlueBet' as its the only cell value with the date referenced.