Formulas returning #Value! in some computers while displaying the results in others

BorisYeltsin

New Member
Joined
Oct 27, 2020
Messages
4
Office Version
  1. 2019
  2. 2007
Platform
  1. Windows
Hello all.

I received as spreadsheet from a client and several cells that uses formula return #Value!, while in this client PC the results are displayed normally.
Tried in different computers, all running 2007 version, but problem persisted.
My first bet was links with other files that I may not have received, but there were none.
Then I tought it could be version issues, but I tried in my home computer (2019 version) and got the same problem.
I tried setting the formula calculation to automatic, tried those option regarding compatibitily with Lotus, all without success.
As a test, I opened the spreadsheet using LibreOffice Calc and the results were displayed correctly.
Can't see an error with the all the formula itself (and, to corroborate that, there is the example of opening without problem on LibreOffice).

An example of formula resulting in error:

=IF(ISNUMBER(U$4),SUMPRODUCT(OFFSET(U41,,-(U$4-U$37),,U$4-U$37+1)+(TRANSPOSE(MMULT((ROW(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(U$4-U$37+1,1)))-ROW(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(1,1)))=(COLUMN(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(1,U$4-U$37+1)))))+0,TRANSPOSE(OFFSET(U85,,-(U$4-U$37),,U$4-U$37+1)))))*-$D26,OFFSET(U98,,-(U$4-U$37),,U$4-U$37+1),INDEX(OFFSET(U$10,,-(U$4-U$37),,U$4-U$37+1),1,COUNTA(OFFSET(U$10,,-(U$4-U$37),,U$4-U$37+1))-COLUMN(OFFSET(U$10,,-(U$4-U$37),,U$4-U$37+1))+COLUMN(OFFSET(U$10,,-(U$4-U$37)))))/1,000,0)

Searched for similar problem, but haven't found it.
Can someone help me? Thanks in advance.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,438
Office Version
  1. 365
Platform
  1. Windows
The thousand separator at the end of the formula is the only thing that stands out as a potential issue, I didn't think that it was even possible to enter a formula with one but if you have managed it somehow then it is creating an invalid number of arguments for the IF function.
 

BorisYeltsin

New Member
Joined
Oct 27, 2020
Messages
4
Office Version
  1. 2019
  2. 2007
Platform
  1. Windows
The thousand separator at the end of the formula is the only thing that stands out as a potential issue, I didn't think that it was even possible to enter a formula with one but if you have managed it somehow then it is creating an invalid number of arguments for the IF function.

Thanks for the response. Makes sense.
I don't know if the separator is there because I used the function translator to post the formula here (the version of office that I use is in portuguese).
I'll check this potential issue and come back with the results.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,438
Office Version
  1. 365
Platform
  1. Windows
If you use semicolon for formulas instead of comma then that would explain it, going between versions of excel it should change automatically to reflect the correct settings (making the comma in the formula decimal separator instead of a thousands separator) but the function translator would not have picked it up.

That only leaves the possibility of the formula attempting to perform a mathematical calculation, for example if U4 is numeric but U37 is not.
 

BorisYeltsin

New Member
Joined
Oct 27, 2020
Messages
4
Office Version
  1. 2019
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

The thousand separator at the end of the formula is the only thing that stands out as a potential issue, I didn't think that it was even possible to enter a formula with one but if you have managed it somehow then it is creating an invalid number of arguments for the IF function.
As I suspected, the separator came from the function translator.
For reference, the original formula is as follow (portuguese version of Excel):

=SE(ÉNÚM(U$4); SOMARPRODUTO(DESLOC(U41;;-(U$4-U$37);;U$4-U$37+1)+(TRANSPOR(MATRIZ.MULT((LIN(INDIRETO(ENDEREÇO(1;1)&":"&ENDEREÇO(U$4-U$37+1;1)))-LIN(INDIRETO(ENDEREÇO(1;1)&":"&ENDEREÇO(1;1)))
=(COL(INDIRETO(ENDEREÇO(1;1)&":"&ENDEREÇO(1;U$4-U$37+1)))))+0;TRANSPOR(DESLOC(U85;;-(U$4-U$37);;U$4-U$37+1)))))*-$D26; DESLOC(U98;;-(U$4-U$37);;U$4-U$37+1); ÍNDICE(DESLOC(U$10;;-(U$4-U$37);;U$4-U$37+1);1;CONT.VALORES(DESLOC(U$10;;-(U$4-U$37);;U$4-U$37+1))-COL(DESLOC(U$10;;-(U$4-U$37);;U$4-U$37+1))+COL(DESLOC(U$10;;-(U$4-U$37)))))/1000;0)
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,438
Office Version
  1. 365
Platform
  1. Windows
Have you checked that U37 contains a numeric value?
Is it the same file with the exact same data that is causing the problem, or is the data different?
Do the problem computers have different settings (UK or US regional settings instead of Portuguese)? If the source data has numbers that are formatted as text and being coerced to values then different regional settings may not read the data correctly.

Have you tried stepping through the formula with the evaluation tool to try and identify the source of the error?
 

BorisYeltsin

New Member
Joined
Oct 27, 2020
Messages
4
Office Version
  1. 2019
  2. 2007
Platform
  1. Windows
Have you checked that U37 contains a numeric value?
Is it the same file with the exact same data that is causing the problem, or is the data different?
Do the problem computers have different settings (UK or US regional settings instead of Portuguese)? If the source data has numbers that are formatted as text and being coerced to values then different regional settings may not read the data correctly.

Have you tried stepping through the formula with the evaluation tool to try and identify the source of the error?

U37 contain a numeric value.
It is the exact same file. Opened it the same way I received it. And just the same with LibreOffice, where I got the results.
AFAIK, my client computer has the same regional settings, but I will try to confirm that. Alas, I already tried changing the regional settings to English (US), without luck.

I already user the evaluation tool to retrace the steps of the formula, but I'll do again as I'm not 100% sure I went all the way (there are other cells with the problem, with other formula, just got this one as an example, it's a big spreadsheet).

I'll reply as soon as I'm able to check it.

Again, thanks for the attention.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,477
Messages
5,624,981
Members
416,065
Latest member
meiravmeron

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top