=IF( and calculations within the same function

[thirdeye85]

I have a question about nesting calculations within the same =IF( function. For instance, I10 has a nested (IF function as follows: =IF($E10=I$1,$G10,IF($F10=I$1,-$H10,0))

This basically says that if the cell $E10=I$1 then G10 will be a positive number and if $F10=I$1 then it will be a negative. This functions well in its own right. However, I would like to attach the following calculation to this function (J14 within spreadsheet) =$G10-($G10*0.026)-0.1. This should only be calculated if B10 = CC, otherwise the calculation should not be performed and only the first two IF logical tests should apply.

I think all I need to do is add a third logical test that states that EVEN IF the first two are true, the last test given that B10=CC will supersede the previous arguments.

Am I going at this the right way?

And just some context the formula is a calculation of credit card fees that are tacked onto a dollar amount of $80.00; if CC is not input under type (B10) but rather CASH then the formula would not be required to calculate those fees.

Thank you for your help!

Double Entry Bookkeeping Final.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Notes	Type	Month	Date	Account Debited	Account Credited	Debit	Credit	Income	SouthState
2
3
4
5
6
7
8
9
10	Doe, Joe	CC	8	9/17/21	Southstate	Income	$ 80.00	$ 80.00	-$80.00	$80.00
11	Doe, Jane	CASH	8	9/17/21	Southstate	Income	$ 80.00	$ 80.00	-$80.00	$80.00
12
13
14										$ 77.82
Sheet2

Cell Formulas
Range	Formula
I10:J11	I10	=IF($E10=I$1,$G10,IF($F10=I$1,-$H10,0))
C10:C11	C10	=MONTH(Ledger3[@Date])
J14	J14	=$G10-($G10*0.026)-0.1

 

[maabadi]

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Notes	Type	Month	Date	Account Debited	Account Credited	Debit	Credit	Income	SouthState
2
3
4
5
6
7
8
9
10	Doe, Joe	CC	=MONTH(Ledger3[@Date])	9/17/21	Southstate	Income	80	80	-80	80		77.72
11	Doe, Jane	CASH	=MONTH(Ledger3[@Date])	9/17/21	Southstate	Income	80	80	-80	80		80
12
13
14										77.82
15
DataSheet1

Cell Formulas
Range	Formula
I10:J11	I10	=IF($E10=I$1,$G10,IF($F10=I$1,-$H10,0))
L10:L11	L10	=(IF($E10=J$1,$G10,IF($F10=J$1,-$H10,0)))*(IF(B10="CC",0.984,1))-IF(B10="CC",1,0)
J14	J14	=$G10-($G10*0.026)-0.1

 

[Toadstool]

Hi Thirdeye85,

Is this what you wanted?

Thirdeye85.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Notes	Type	Month	Date	Account Debited	Account Credited	Debit	Credit	Income	SouthState
2										0
3										0
4										0
5										0
6										0
7										0
8										0
9										0
10	Doe, Joe	CC	9	9/17/2021	Southstate	Income	80	80	-80	77.82
11	Doe, Jane	CASH	9	9/17/2021	Southstate	Income	80	80	-80	80
Sheet1

Cell Formulas
Range	Formula
J2:J11	J2	=IF([@Type]="CC",$G2-($G2*0.026)-0.1,IF($E2=J$1,$G2,IF($F2=J$1,-$H2,0)))
I10:I11	I10	=IF($E10=I$1,$G10,IF($F10=I$1,-$H10,0))
C10:C11	C10	=MONTH([@Date])

 

[Fluff]

Another option

=IF(B10="CC",$G10-($G10*0.026)-0.1,I10)

 

[thirdeye85]

Hi Thirdeye85,

Is this what you wanted?

Thirdeye85.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Notes	Type	Month	Date	Account Debited	Account Credited	Debit	Credit	Income	SouthState
2										0
3										0
4										0
5										0
6										0
7										0
8										0
9										0
10	Doe, Joe	CC	9	9/17/2021	Southstate	Income	80	80	-80	77.82
11	Doe, Jane	CASH	9	9/17/2021	Southstate	Income	80	80	-80	80
Sheet1

Cell Formulas
Range	Formula
J2:J11	J2	=IF([@Type]="CC",$G2-($G2*0.026)-0.1,IF($E2=J$1,$G2,IF($F2=J$1,-$H2,0)))
I10:I11	I10	=IF($E10=I$1,$G10,IF($F10=I$1,-$H10,0))
C10:C11	C10	=MONTH([@Date])

I tried your formula and it says that "The Syntax of this name isn't correct". Is the @Type a placement for something I should input?

 

[thirdeye85]

Another option

=IF(B10="CC",$G10-($G10*0.026)-0.1,I10)

Unfortunately, this did not keep the continuity of the other formula, =IF($E10=I$1,$G10,IF($F10=I$1,-$H10,0)). Thank you for the reply.

 

[thirdeye85]

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Notes	Type	Month	Date	Account Debited	Account Credited	Debit	Credit	Income	SouthState
2
3
4
5
6
7
8
9
10	Doe, Joe	CC	=MONTH(Ledger3[@Date])	9/17/21	Southstate	Income	80	80	-80	80		77.72
11	Doe, Jane	CASH	=MONTH(Ledger3[@Date])	9/17/21	Southstate	Income	80	80	-80	80		80
12
13
14										77.82
15
DataSheet1

Cell Formulas
Range	Formula
I10:J11	I10	=IF($E10=I$1,$G10,IF($F10=I$1,-$H10,0))
L10:L11	L10	=(IF($E10=J$1,$G10,IF($F10=J$1,-$H10,0)))*(IF(B10="CC",0.984,1))-IF(B10="CC",1,0)
J14	J14	=$G10-($G10*0.026)-0.1

Not quite sure I understand the "(IF(B10="CC",0.984,1))-IF(B10="CC",1,0)". Thank you for the reply.

 

[Alex Blakenburg]

Try:

=IF($B10="CC",$G10-($G10*0.026)-0.1,IF($E10=I$1,$G10,IF($F10=I$1,-$H10,0)))

 

[Toadstool]

I tried your formula and it says that "The Syntax of this name isn't correct". Is the @Type a placement for something I should input?

I saw you had column D called Date so I assumed you had column B called Type. Here it is with cell address instead.

Thirdeye85.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Notes	Type	Month	Date	Account Debited	Account Credited	Debit	Credit	Income	SouthState
2										0
3										0
4										0
5										0
6										0
7										0
8										0
9										0
10	Doe, Joe	CC	9	9/17/2021	Southstate	Income	80	80	-80	77.82
11	Doe, Jane	CASH	9	9/17/2021	Southstate	Income	80	80	-80	80
Sheet1

Cell Formulas
Range	Formula
J2:J11	J2	=IF(B2="CC",$G2-($G2*0.026)-0.1,IF($E2=J$1,$G2,IF($F2=J$1,-$H2,0)))
I10:I11	I10	=IF($E10=I$1,$G10,IF($F10=I$1,-$H10,0))
C10:C11	C10	=MONTH([@Date])

 

[thirdeye85]

Thanks guys we are getting closer however still no cigar, but I think I know what the problem is.

What I need the formula to do is to incorporate the logic of the later half of this equation into the first part, so that if CC is triggered in the B column it is following the logic of the 2nd half of the equation which converts column I or J (in your example Toadstool) into a positive or negative number. There is always going to be a positive or negative number since the transaction needs to be balanced. Currently if the first logical test is true it does not incorporate the second half of the logic in the equation and if CC is triggered this will only result in a positive number, which is an incorrect result.

It would need to look something like this:

If CC is not triggered in column B. If (E10=I1 then positive number in G10), If (F10 = I1 then negative number in H10). This is currently working by itself.

If CC is triggered in column B then. G10 - ($G10*0.026)-0.1, If (E10=I1 then positive number in G10, if (F10 = I1 then negative number in H10). This is not working as when the formula scans the logic it just reverts to the first part of the formula G10 - ($G10*0.026)-0.1. The more that I think about it we are not instructing the formula in "G10 - ($G10*0.026)-0.1" to change anything to a negative number in the H10 column if CC is trigged and this is what I want it to do if F10 = I1.

Would adding H10 - ($H10*0.026)-0.1 to somewhere in the formula make this work?