CODEWITHSUNDEEP
excel-formula
user3094542
user3094542

Reputation: 11

How do I use product formula within if else condition?

A1       B1   C1    D1     E1    F1

Maths    5   44    High    ?     ?
  1. I want to display the formula in E1
  2. corresponding result in F1 cell.

scenario is if A1 cell is 'maths' and D1 cell is 'High' then I want to multiply B1 value with 7 (B1*7) otherwise `(B1*10)è

if(AND(A1="Maths",D1="High") product(B1*7))

I'm trying to use the product formula within if condition like above. But I'm getting error message. can anyone help me?

Upvotes: 0

Views: 960

Answers (2)

SeanC
SeanC

Reputation: 15923

the formula I would use in your example would be 

=PRODUCT(B1,IF(AND(A1="Maths",D1="High"),7,10))

to display the formula, there are 3 options, depending on the version of excel.

In excel 2013, you can use =FORMULATEXT(F1) msdn
In previous versions, you could either copy the formula, then put a single quote mark in front of the formula, or you could create a UDF (which would then work the same way as the Excel 2013 version.
(UDF Code:

Function GetFormula(CellRef as Range) as String
   GetFormula = CellRef.Formula
End Function

), and use =GetFormula(F1)

Upvotes: 0

LPH
LPH

Reputation: 1295

I hope i understood you right: if the String "Maths" is in the A1 cell AND the String "High" is in the D1 Cell, you want to procude the product of B1*7; if the String "Maths" is not in the A1 cell OR the String "High" is not in the D1 cell, you want to produce the product of B1*10;

well you could use the following attempt:

  =IF(AND(A1="Maths";D1="High");PRODUCT(B1*7);PRODUCT(B1*10))

If you want to split the products in the specific way, that the Product PRODUCT(B1*7) is in the E1 cell, and the Product PRODUCT(B1*10) is in the F1 cell, you could use the following code:

E1  =IF(AND(A1="Maths";D1="High");PRODUCT(B1*7);"No result")
F1  =IF(AND(A1="Maths";D1="High");"No result";PRODUCT(B1*10))

Upvotes: 1

Related Questions