If Statement Formula Syntax

[erniepoe]

Hi All,

I have what I think might be the dumbest question ever posed here, but I'm at my wits end. I have the following two if statements, both of which work individidually on their own. However, I am trying to add the first to the second as another condition, but when i do that, no matter where in the formula i try to insert it, it tells me i have entered too many arguments. I have tried adding a ",0)" to the end of formula 1, but am still getting an error.

My guess is somewhere along the line i have two many or two few parenthesis inserted, but after 4 hours of trying to troubleshoot every combination, I am giving up.

A million Schrute Bucks if you can help me consolidate these two formulas.

In the attached image, the $399k is formula in row 30 is formula 2, while the $333k in row 26 is formula 1.


1)
=IF(AND(MONTH(V$1)=MONTH($E30),YEAR(V$1)=YEAR($E30)),(INDEX($V$2:$DA$4,MATCH($G30,$I$2:$I$4,0),MATCH(V$28,$V$1:$DA$1,0))+$H30))*$A30/12*(DAY(EOMONTH(V1,0)-DAY($E30))/30)

2) =IF(AND(MONTH(V$1)=MONTH($F30),YEAR(V$1)=YEAR($F30)),-$A30*IF($G30="Sonia",INDEX($J$5:$DA$6,MATCH("GBP",$I$5:$I$6,0),MATCH(V$28,$J$1:$DA$1,0)),IF($G30="Euribor",INDEX($J$5:$DA$6,MATCH("EUR",$I$5:$I$6,0),MATCH(V$28,$J$1:$DA$1,0)),1)),
IF(YEAR(V$1)>YEAR($F30),0,
IF(YEAR(V$1)<YEAR($E30),0,
IF(AND(MONTH(V$1)<=MONTH($E30),YEAR(V$1)<YEAR($E30)),0,
IF(AND(MONTH(V$1)>=MONTH($F30),YEAR(V$1)>=YEAR($F30)),0,

IF($C30="Fixed",$A30*($D30/12),$A30*(INDEX($J$2:$DA$4,MATCH($G30,$I$2:$I$4,0),MATCH(V$28,$J$1:$DA$1,0))+$H30)/12*IF($G30="Sonia",INDEX($J$5:$DA$6,MATCH("GBP",$I$5:$I$6,0),MATCH(V$28,$J$1:$DA$1,0)),IF($G30="Euribor",INDEX($J$5:$DA$6,MATCH("EUR",$I$5:$I$6,0),MATCH(V$28,$J$1:$DA$1,0)),1))))))))

 

[DanteAmor]

Check if it is what you need. At least the 2 results of the 2 formulas for me are the same. I did not test with all the data.

Maybe if you provide an example of your data with XL2BB tool, and also tell us what result you expect. We could review the correct functioning of the formula.

varios 16jun2023.xlsm
	V
26	399,750
27
28	01-ene
29
30	399,750
Hoja5

Cell Formulas
Range	Formula
V26	V26	=IF(AND(MONTH(V$1)=MONTH($E30),YEAR(V$1)=YEAR($E30)),(INDEX($V$2:$DA$4,MATCH($G30,$I$2:$I$4,0),MATCH(V$28,$V$1:$DA$1,0))+$H30))*$A30/12*(DAY(EOMONTH(V1,0)-DAY($E30))/30)
V30	V30	=IF(AND(MONTH(V$1)=MONTH($F30),YEAR(V$1)=YEAR($F30)),-$A30*IF($G30="SONIA",INDEX($J$5:$DA$6,MATCH("GBP",$I$5:$I$6,0),MATCH(V$28,$J$1:$DA$1,0)),IF($G30="EURIBOR",INDEX($J$5:$DA$6,MATCH("EUR",$I$5:$I$6,0),MATCH(V$28,$J$1:$DA$1,0)),1)), IF(YEAR(V$1)>YEAR($F30),0, IF(YEAR(V$1)<YEAR($E30),0, IF(AND(MONTH(V$1)<=MONTH($E30),YEAR(V$1)<YEAR($E30)),0, IF(AND(MONTH(V$1)>=MONTH($F30),YEAR(V$1)>=YEAR($F30)),0, IF(AND(MONTH(V$1)=MONTH($E30),YEAR(V$1)=YEAR($E30)),(INDEX($V$2:$DA$4,MATCH($G30,$I$2:$I$4,0),MATCH(V$28,$V$1:$DA$1,0))+$H30)*$A30/12*(DAY(EOMONTH(V1,0)-DAY($E30))/30), IF($C30="Fixed",$A30*($D30/12),$A30*(INDEX($J$2:$DA$4,MATCH($G30,$I$2:$I$4,0),MATCH(V$28,$J$1:$DA$1,0))+$H30)/12*IF($G30="SONIA",INDEX($J$5:$DA$6,MATCH("GBP",$I$5:$I$6,0),MATCH(V$28,$J$1:$DA$1,0)),IF($G30="EURIBOR",INDEX($J$5:$DA$6,MATCH("EUR",$I$5:$I$6,0),MATCH(V$28,$J$1:$DA$1,0)),1)))))))))

Note XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.​

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​