mardi 21 mai 2019

How to make a condition based on the number of decimals a number has with the IFS and AND functions in Google Sheets?

I'm trying to do the following in Google Sheets:

If the number in cell A1 has 4 decimals (for example "1.0001"), and if cell A1>B1 (for example A1=1.0001 and B1=0.0001), and if the string in cell C1 = "Good", then return (A1-B1)*10000.

Additionally:

If the number in cell A2 has 2 decimals (for example "1.01"), and if cell A2>B2 (for example A2=1.01 and B2=0.01), and if the string in cell C2 = "Great", then return (A2-B2)*100.

So far, I've come up with this IFS function:

=IFS((AND(A1>B1, C1="Good")), (A1-B1)*10000,(AND(A2>B2, C2="Great")),(A2-B2)*100,TRUE,"ERROR")

Which treats the two arguments A1>B1, C1="Good" / A2>B2, C2="Great", within the AND formula.

How can I add the decimal argument to the AND statement?

I thought of setting for something like :

=IFS((AND(A1>B1, C1="Good", **A1=(a number with 4 decimals)))**, (A1-B1)*10000,(AND(A2>B2, C2="Great", **A2=(a number with 2 decimals)))**,(A2-B2)*100,TRUE,"ERROR")

Where the statements:

A1=(a number with 4 decimals)

and

A1=(a number with 2 decimals)

would do the trick.

How do you formulate those missing "decimal statements"?

Aucun commentaire:

Enregistrer un commentaire