Nested IF

[saialkesh]

Dear team,

Can someone please help me for a nested if formula. I need to write a formula enable to do the following conditions in one if statement -

No	Buy Currency	Sell Currency	Purchase Value (Column M)	Sell Value (Column O)	Notional value (Column V)	Formula	Notes
1	EUR	USD				0+ V-M * -1	If buy currency is EUR, and sale currency is USD, then formula is zero + notional – sale *-1
2	EUR	GBP				0+ V-M *-1 *1.28	If buy currency is EUR, and sale currency is GBP, then formula is zero + notional – sale *-1 * 1.28
3	USD	EUR				0+ V-M *-1	If buy currency is USD, and sale currency is EUR, then formula is zero + notional – sale *-1
4	USD	GBP				0+ V-M *-1	If buy currency is USD, and sale currency is GBP, then formula is zero + notional – sale *-1
5	GBP	EUR				0+ V-M *-1 *1.28	If buy currency is GBP, and sale currency is EUR, then formula is zero + notional – sale *-1 * 1.28
6	GBP	USD				0+ V_M *-1	If buy currency is GBP, and sale currency is USD, then formula is zero + notional – sale *-1
7	Any other	EUR				V-O *-1 +0	If buy currency is any other than EUR and sale currency is EUR, then formula is notional – sale *-1 +zero
8	Any other	USD				V-O *-1 +0	If buy currency is any other than USD and sale currency is EUR, then formula is notional – sale *-1 +zero
9	Any other	GBP				V-O *-1 +0	If buy currency is any other than GBP and sale currency is EUR, then formula is notional – sale *-1 +zero
10	EUR	Any other				0+ V-M *-1 *1.17	If buy currency is EUR and sale currency is any other than EUR, then formula is zero + notional – purchase *-1 * 1.17
11	USD	Any other				0+ V-M *-1	If buy currency is USD and sale currency is any other than USD, then formula is zero + notional – purchase *-1
12	GBP	Any other				0+ V-M *-1 *1.28	If buy currency is GBP and sale currency is any other than GBP, then formula is zero + notional – purchase *-1 * 1.28

Appreciate all the help.

Thank you.

 

[ER_Neha]

Hello, Your condition for buy currency = usd & sell currency = eur have two formula one in row 3rd and other in row 8th. kindly check your condition and clarify your requirement so that we can help you better.

 

[saialkesh]

Hello, Your condition for buy currency = usd & sell currency = eur have two formula one in row 3rd and other in row 8th. kindly check your condition and clarify your requirement so that we can help you better.

Hello Neha,
Thank you for the revert. There's a typo on row # 8 and my apologies

For row #8, the condition is if buy currency is any currency other than USD and Sell currency is USD.

Hope that clarifies.

 

[saialkesh]

Dear team,

Can someone please help me for a nested if formula. I need to write a formula enable to do the following conditions in one if statement -

No	Buy Currency	Sell Currency	Purchase Value (Column M)	Sell Value (Column O)	Notional value (Column V)	Formula	Notes
1	EUR	USD				0+ V-M * -1	If buy currency is EUR, and sale currency is USD, then formula is zero + notional – sale *-1
2	EUR	GBP				0+ V-M *-1 *1.28	If buy currency is EUR, and sale currency is GBP, then formula is zero + notional – sale *-1 * 1.28
3	USD	EUR				0+ V-M *-1	If buy currency is USD, and sale currency is EUR, then formula is zero + notional – sale *-1
4	USD	GBP				0+ V-M *-1	If buy currency is USD, and sale currency is GBP, then formula is zero + notional – sale *-1
5	GBP	EUR				0+ V-M *-1 *1.28	If buy currency is GBP, and sale currency is EUR, then formula is zero + notional – sale *-1 * 1.28
6	GBP	USD				0+ V_M *-1	If buy currency is GBP, and sale currency is USD, then formula is zero + notional – sale *-1
7	Any other	EUR				V-O *-1 +0	If buy currency is any other than EUR and sale currency is EUR, then formula is notional – sale *-1 +zero
8	Any other	USD				V-O *-1 +0	If buy currency is any other than USD and sale currency is EUR, then formula is notional – sale *-1 +zero
9	Any other	GBP				V-O *-1 +0	If buy currency is any other than GBP and sale currency is EUR, then formula is notional – sale *-1 +zero
10	EUR	Any other				0+ V-M *-1 *1.17	If buy currency is EUR and sale currency is any other than EUR, then formula is zero + notional – purchase *-1 * 1.17
11	USD	Any other				0+ V-M *-1	If buy currency is USD and sale currency is any other than USD, then formula is zero + notional – purchase *-1
12	GBP	Any other				0+ V-M *-1 *1.28	If buy currency is GBP and sale currency is any other than GBP, then formula is zero + notional – purchase *-1 * 1.28

Appreciate all the help.

Thank you.

Sorry for the typo -

Row 8 is if Buy currency is any other currency than USD and Sell currency is USD
Row 9 is if Buy currency is any other currency than GBP and Sell currency is GBP

 

[AlanY]

solution with a helper column X

Book1
	A	B	C										M		O							V	W	X
1	No	Buy Currency	Sell Currency										Purchase Value (Column M)		Sell Value (Column O)							Notional value (Column V)	Formula
2	1	EUR	USD										100		200							300	400	1
3	2	EUR	GBP										100		200							300	428	1.28
4	3	USD	EUR										100		200							300	400	1
5	4	USD	GBP										100		200							300	400	1
6	5	GBP	EUR										100		200							300	428	1.28
7	6	GBP	USD										100		200							300	400	1
8	7	Any other	EUR										100		200							300	500	1
9	8	Any other	USD										100		200							300	500	1
10	9	Any other	GBP										100		200							300	500	1
11	10	EUR	Any other										100		200							300	417	1.17
12	11	USD	Any other										100		200							300	400	1
13	12	GBP	Any other										100		200							300	428	1.28
Sheet1

Cell Formulas
Range	Formula
W2:W13	W2	=V2+IF(OR(B2={"EUR","USD","GBP"}),M2*X2,O2)

 

[ER_Neha]

Hello, I am not able to get your formula properly, but i think you can rectify that in the below code as per your need. Please let me know if this code works well for you.

Sub nested_if()
    
Dim i As Integer
    
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    If Range("B" & i).Value = "EUR" Then
        Select Case Range("C" & i).Value
            Case "USD": Range("G" & i).Value = 0 + Range("V" & i).Value - (Range("M" & i).Value) * -1
            Case "GBP": Range("G" & i).Value = 0 + Range("V" & i).Value - ((Range("M" & i).Value) * -1) * 1.28
            Case "Any other": Range("G" & i).Value = 0 + Range("V" & i).Value - ((Range("M" & i).Value) * -1) * 1.17
        End Select
            
    ElseIf Range("B" & i).Value = "USD" Then
        Select Case Range("C" & i).Value
            Case "EUR": Range("G" & i).Value = 0 + Range("V" & i).Value - (Range("M" & i).Value) * -1
            Case "GBP": Range("G" & i).Value = 0 + Range("V" & i).Value - (Range("M" & i).Value) * -1
            Case "Any other": Range("G" & i).Value = 0 + Range("V" & i).Value - (Range("M" & i).Value) * -1
        End Select
    ElseIf Range("B" & i).Value = "GBP" Then
        Select Case Range("C" & i).Value
            Case "USD": Range("G" & i).Value = 0 + Range("V" & i).Value - (Range("M" & i).Value) * -1
            Case "EUR": Range("G" & i).Value = 0 + Range("V" & i).Value - ((Range("M" & i).Value) * -1) * 1.28
            Case "Any other": Range("G" & i).Value = 0 + Range("V" & i).Value - ((Range("M" & i).Value) * -1) * 1.28
        End Select
    Else
        Select Case Range("C" & i).Value
            Case "EUR": Range("G" & i).Value = Range("V" & i).Value - (Range("O" & i).Value) * -1 + 0
            Case "GBP": Range("G" & i).Value = Range("V" & i).Value - (Range("O" & i).Value) * -1 + 0
            Case "USD": Range("G" & i).Value = Range("V" & i).Value - (Range("O" & i).Value) * -1 + 0
        End Select
    End If
Next i

End Sub