F5F Stay Refreshed Software General Software Formula to calculate total amount in any format

Formula to calculate total amount in any format

Formula to calculate total amount in any format

K
kmurray
Member
Find
222
08-29-2023, 03:56 AM
#1
The formula should work for all entries in column amount 2, no matter their size.
In cell e7 of the "daily" sheet, when a number is entered with thousands separated (like '1.000.00'), the current formula causes an error.
You need a formula that handles this formatting correctly, even with amounts like '1.000.00'.
Please let me know if you need further clarification.
Reply
Reply
K
kmurray
08-29-2023, 03:56 AM #1

The formula should work for all entries in column amount 2, no matter their size.
In cell e7 of the "daily" sheet, when a number is entered with thousands separated (like '1.000.00'), the current formula causes an error.
You need a formula that handles this formatting correctly, even with amounts like '1.000.00'.
Please let me know if you need further clarification.

Reply
Reply
C
Cecelmat
Member
Find
161
09-01-2023, 05:56 AM
#2
Because you included a leading quote, the system recognizes that the value here is a character string, not a numerical one.
Reply
Reply
C
Cecelmat
09-01-2023, 05:56 AM #2

Because you included a leading quote, the system recognizes that the value here is a character string, not a numerical one.

Reply
Reply
P
puppydemon20
Member
Find
165
09-06-2023, 03:28 AM
#3
the input is formatted as text only, so numbers appear as dots instead of amounts. this happens because google sheet interprets the dots as time markers rather than numerical values.
Reply
Reply
P
puppydemon20
09-06-2023, 03:28 AM #3

the input is formatted as text only, so numbers appear as dots instead of amounts. this happens because google sheet interprets the dots as time markers rather than numerical values.

Reply
Reply
S
Salty_Cactus1
Member
Find
205
09-06-2023, 05:49 AM
#4
Enter ALL numbers without periods commas quotes or any other extraneous characters
Reply
Reply
S
Salty_Cactus1
09-06-2023, 05:49 AM #4

Enter ALL numbers without periods commas quotes or any other extraneous characters

Reply
Reply
X
xXSuperNovaXx
Posting Freak
Find
811
09-06-2023, 10:45 AM
#5
And let the program arrange the cells in your preferred way.
Reply
Reply
X
xXSuperNovaXx
09-06-2023, 10:45 AM #5

And let the program arrange the cells in your preferred way.

Reply
Reply
R
RulwenJr
Posting Freak
Find
786
09-07-2023, 08:44 AM
#6
I resolve the issue using this formula
=arrayformula( ifs( VAL.VUOTO(L5:L); SE.ERRORE(1/0); VAL.SSIZE(L5:L); VAL.VALORE(L5:L); VAL.NUMERO(SE.ERRORE(VALORE(SOSTITUISCI(regexreplace(L5:L; "\.(\d\d)$"; ",$1"); "."; "")))); VAL.VALORE(SOSTITUISCI(regexreplace(L5:L; "\.(\d\d)$"; ",$1"); "."; "")); VERO; L5:L ) )
Reply
Reply
R
RulwenJr
09-07-2023, 08:44 AM #6

I resolve the issue using this formula
=arrayformula( ifs( VAL.VUOTO(L5:L); SE.ERRORE(1/0); VAL.SSIZE(L5:L); VAL.VALORE(L5:L); VAL.NUMERO(SE.ERRORE(VALORE(SOSTITUISCI(regexreplace(L5:L; "\.(\d\d)$"; ",$1"); "."; "")))); VAL.VALORE(SOSTITUISCI(regexreplace(L5:L; "\.(\d\d)$"; ",$1"); "."; "")); VERO; L5:L ) )

Reply
Reply