Good evening, I am reaching out because I have not been able to find an answer to my problem. Let me explain: - Column A can contain either a number or text (which is variable) - Column B contains a number - Column C (this is where it gets tricky) should contain: if A is a number, the result of A*B, and if A is text, then we put 0. My problem is that I don’t know how to translate the following phrase into Excel language: "If Ax is a number then do Ax*Bx otherwise put 0". I know that I need to use the IF function (condition; value if true; value if false). Can you help me translate the condition? Thank you for the help you will provide. Good night to all Anixela

[Excel] Formula Cell with Number and Text : CCM

Réponse 1 / 10

gbinforme Posted messages 14930 Registration date Status Contributeur Last intervention 4 742

Hello

This formula should suit you. We check if A is numeric ISNUMBER(A1) and if so, we multiply, otherwise zero.

=IF(ISNUMBER(A1),B1*A1,0)

always zen

jeanchristof Posted messages 1 Status Membre

Hello, New to this forum, I'm sorry to revisit this old and excellent response, but it doesn't work every time. In my case, cell A1 is a number coming from a "Alea()" function... the result of my multiplication (B1*A1) is FALSE two out of three times, either PLUS or MINUS. I can't seem to find a solution. Thank you in advance to the experts for your response. JC

The Engineer

Hello,
I’m returning to this old question.
In my humble opinion, it’s simpler to use the “N” function.
The formula becomes, in column C1:
=N(A1)*B1
…if I’m not mistaken.

mary

You're welcome :)

maged

thank you

Réponse 2 / 10

Nénou

Bonjour, je dois faire une facture avec Excel. Le problème de mes formules de calcul se pose lorsque je veux insérer des valeurs à mes chiffres puis de faire leur calcul. Exemple : je veux faire le produit de 10m² x 20€. Si dans la cellule A1 je mets 10 et dans la cellule B1 je mets 20, alors dans la cellule C1 j'obtiens 200 avec la formule : =A1*B1. Par contre, si je précise les valeurs m² et € respectivement dans les cellules A1 et B1, alors dans C1 j'obtiens : #VALEUR ! Comment faire pour obtenir le résultat des calculs en mettant dans les cellules A1 et B1 les valeurs 10m² et 20€ pour obtenir dans C1 = 200€ ?

Merci par avance pour votre aide capitale pour mon travail.

Nénou

Vaucluse Posted messages 27336 Registration date Status Contributeur Last intervention 6 453

Hello
For euros, there's no problem; Excel knows how to handle it, you just need to format your cells
For square meters, it's trickier, but is it really necessary?
Actually, if you insist, here’s a solution:
Assuming that:
1°) Your square meters are always indicated without spaces and without a comma between the number and m2
2°) that you don't have more than 4 digits without a comma in the area number,
You can:
Your area in A1, your price per square meter in B1:

In C1
=IF(LEN(A1)=3,LEFT(A1,1),IF(LEN(A1)=4,LEFT(A1,2),IF(LEN(A1)=5,LEFT(A1,3),IF(LEN(A1)=6,LEFT(A1,4),0)))*B1

With this, you should multiply the numerical value of your area by the amount per square meter in €

Be careful, this formula stops at 4 digits for the area; it can increment further up to the limit of 7 "IF" statements in a row.
In fact, the formula subtracts the total number of characters in cell A1, the number of characters specifying m2 (that is, 2 characters)
But the big issue is that any spaces and commas are counted as characters and thus distort the result. (we can find a way to bypass the presence of the comma by fixing the number of characters to a minimum of 5, but it forces you to always enter a value such as: 20.00m2

I think, after this exercise in style that serves little purpose, that it is definitely preferable to put m2 as a column title and not write it for each value entry.

Or you could

use an intercalary column to indicate m2. Your calculation will then be done by multiplying A1 and C1 in D1, and column B will permanently carry the m2 info;

Just in case to enter this info based on the filling of A1:
In B1:
=IF(A1>0,"m2","")and put the price per square meter in € in column C.
CRDLMNT

--
Truth is not possessed; it is only sought.

Nados

You need to create a custom format for the cells in column A, the format I created is:

#.##"m²"

and the format for column B exists in numbers, in your case, EURO France

the result is automatically formatted in EURO

the cell contents in formula mode:

123 20 =A1*B1
123 15 =A2*B2
34 18 =A3*B3
345 22 =A4*B4
345 18 =A5*B5
7 19 =A6*B6

here is what you want I believe:

123m² 20,00 € 2.460,00 €
123m² 15,00 € 1.845,00 €
34m² 18,00 € 612,00 €
345m² 22,00 € 7.590,00 €
345m² 18,00 € 6.210,00 €
7m² 19,00 € 133,00 €

Vaucluse Posted messages 27336 Registration date Status Contributeur Last intervention 6 453 > Nados

Good evening Nados
And well done, I didn't know that one...... (it's true that I know very few!)
Thanks again, at least for me, if not for the requester.
CRDLMNT
--
The truth is not possessed, it is only sought.

Nados > Vaucluse Posted messages 27336 Registration date Status Contributeur Last intervention

With pleasure!
It was nothing much...

Have a great day!

Steve

zebu45 > Nados

Good evening,

I just saw that you are knowledgeable about formulas.

It may be a basic question, but I don't know how to create the following formula:

- I have created an invoice,
- I have a "quantity" column where I enter 1 - 1.5 - 2... and a "unit price" column in €
- next to it, I obviously have a "total" column. In this column, I entered the simple formula =SUM(B12*C12) for example, but it didn't work.

If I put € in the quantity, it works, and vice versa if I put normal numbers instead of € it works.

So what is the formula to multiply a number with € if I can explain?

Réponse 3 / 10

anixela Posted messages 252 Registration date Status Membre Last intervention 23

Good evening,

Thank you very much...you’re saving my life (well, maybe not) but at least my night!
I also knew that this thing (ISNUMBER) existed but couldn't find it in the help!!!

Thanks again

Nados

Hi!

You can also keep it simpler!

=N(A1)*B1

The N function returns the value if it's a number; if it's text, it returns 0, so by doing the calculation you get your answer!

Bye!

Vaucluse Posted messages 27336 Registration date Status Contributeur Last intervention 6 453 > Nados

Hello...
That's a good answer, but 4 months later, the person concerned may have left!
BCRDLMNT
--
Truth is not possessed, it is only sought.

nados > Vaucluse Posted messages 27336 Registration date Status Contributeur Last intervention

Sorry, I hadn't seen it, I had just signed up for a search and I wanted to do the right thing!!! :)

Anyway, it can be helpful for others!

Bye! :)

Steve

JESS > nados

Hello!

Of course it can help others!!! The proof is that I've been searching all forums for an hour for a way to sum percentages contained in various columns, knowing that sometimes the formula giving me the percentage returns an empty "".
And believe me, looking for the sum of non-contiguous cells that sometimes contain text... it's not easy!!
So more than a year later, this is saving my life!! And it's so simple.

Long live EXCEL

A thousand thanks.

Jess

gbinforme Posted messages 14930 Registration date Status Contributeur Last intervention 4 742 > JESS

Hello

<ital>and believe me, trying to sum discontinuous cells that sometimes contain text... it's no walk in the park!! /ital>

And yet, it's been a long time since we could do it natively in Excel simply with the SUM function?
--

Always zen

Réponse 4 / 10

Steph

Hello and thank you all,

Indeed, some questions are a bit outdated and the answers may sometimes seem delayed, but the problems encountered by some today are or will be the problems encountered by others, today or tomorrow. Therefore, share your experiences, your tips, and bring your solutions; they will always delight the traveler who is searching for their way. Thank you.

Réponse 5 / 10

Vaucluse Posted messages 27336 Registration date Status Contributeur Last intervention 6 453

Hello Anixela, hello gbinforme
Here is the answer to your question with gbinforme.
Just in case for the future, if you don't know it, rather than going through help:
Select the cell to argument and:
Taskbar/insertion/function:
Choose in the box what seems appropriate and follow the info to correctly enter your formula
BCRDLMNT
--

Science without conscience is but the ruin of the soul.

Réponse 6 / 10

anixela Posted messages 252 Registration date Status Membre Last intervention 23

Hello!

Thank you all...
It's surprising to receive replies even though the issue is resolved...

But still, thank you for the additional information... it can always be useful...

Have a great weekend
Anixela

nados

Hi!
Since I'm new, I didn't notice the date and if it was resolved!!!
I apologize!

Have a great day!

Steve :)

Réponse 7 / 10

Eilaza

Hello,

I have a quick question:

I need to calculate cells that contain specific text and not numbers.

Example:

In my cells F8, F9, F10, F11, etc., it says P3 but sometimes these cells might contain x or c

What I need to do is to calculate (to give me a number) the total number of P3s (P3=1) that I have in my column (so F8 + F9, etc., if it says P3) and when it shows x or c, the column is worth nothing

Thank you for your help.

Vaucluse Posted messages 27336 Registration date Status Contributeur Last intervention 6 453

Good evening

To count in a range the number of cells that match the value you are looking for (here P3) for example in a range from F8 to F12
=COUNTIF(F8:F12,"P3")

If you want to assign a fixed value to P3 and get the count result:
=COUNTIF(F8:F12,"P3")*Value of P3

Finally, this formula can be applied simultaneously to a range of several columns, for example:
=COUNTIF(F8:X100,"P3")

CRDLMNT
--
Truth is not possessed, it is only sought.

Réponse 8 / 10

atd49 Posted messages 1 Status Membre 2

Hello

I would like to know if it is possible that from a cell, for example b14 which would contain the formula, I could modify cell c3 based on the text indicated in cell a1

=if(a1="text";c3="another text";"") this formula being in cell b14

Thank you for your understanding

Réponse 9 / 10

gbinforme Posted messages 14930 Registration date Status Contributeur Last intervention 4 742

Hello

The result of the formula is in the cell where it is located: if you want to change C3, you need to put the formula in C3 and not in B14.
--

Always zen

arnaud

I am looking for the answer to the following exercise please:
-2 (3x-9)-7 (2x-1)

x=letter
Urgent

Réponse 10 / 10

Ced0057

Hello,

I need a little help
I would like to display a 1 in I9 if in H9 I have "NP or C or E"

I can get a 1 if I have NP but I can't perform the OR function

=IF(COUNTIF(H9;ʺ*NP*ʺ);ʺ1ʺ;

gbinforme Posted messages 14930 Registration date Status Contributeur Last intervention 4 742

Bonjour,

In French:

=IF(ISERROR(SEARCH("NP",H9)),IF(ISERROR(SEARCH("C",H9)),IF(ISERROR(SEARCH("E",H9)),"",1),1),1)

In English:

=IF(ISERROR(SEARCH("NP",H9)),IF(ISERROR(SEARCH("C",H9)),IF(ISERROR(SEARCH("E",H9)),"",1),1),1)

[Excel] Formula Cell with Number and Text

10 réponses

How to get the character µ on the android keyboard.

Texture issues in enshrouded

Access issue to my "contact me" profile

Windows spotlight is not working.

My tv turns off and then on again repeatedly?

Executable from a python file

Pay in 4 installments cdiscount

The alt gr key is not working on my keyboard.

Code 80072efe how to get windows update working again

Video in ".3gp" format