=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

 

[Alex Blakenburg]

OK try this in cell I10

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

 

[thirdeye85]

This works like a charm, thank you so much!

Can you just briefly explain this top part.
=IF($B10<>"CC", This instructs the formula if B10 does not equal CC, but what is it instructing it to do?

And this bottom part:

IF($F10=I$1,-($H10 - ($H10*0.026)-0.1),0)))

Thank you for your time in helping =)

 

[Alex Blakenburg]

Can you just briefly explain this top part.
=IF($B10<>"CC", This instructs the formula if B10 does not equal CC, but what is it instructing it to do?

This part of the if statement is simply a switch.
You requirement is to use a different formula if B10 = "CC".
For ease of reading the 1st line logic is reversed so that:-
If it B10 is not CC then do your original logic.
=IF($B10<>"CC",
*** not CC so just do the original logic ***
IF($E10=I$1,$G10,IF($F10=I$1,-$H10,0)),
*** ok it is CC and needs the new formula ***
IF($E10=I$1,$G10 - ($G10*0.026)-0.1,IF($F10=I$1,-($H10 - ($H10*0.026)-0.1),0)))

IF($F10=I$1,-($H10 - ($H10*0.026)-0.1),0)))

part of IF($E10=I$1,$G10 - ($G10*0.026)-0.1,IF($F10=I$1,-($H10 - ($H10*0.026)-0.1),0)))

Since G10 and H10 are both positive, you can use the Exact same formula for both,
$G10 - ($G10*0.026)-0.1
$H10 - ($H10*0.026)-0.1
But at the end of that if you use G10 you want it to be the positive and if you used H10 you want it to be negative so take the formula wrap it in () to treat it a single entity and multiply it by -1
-1*($H10 - ($H10*0.026)-0.1)
which you can simplify to
-($H10 - ($H10*0.026)-0.1)

Since your 2nd question included the If statement, this alignment may help

 

[thirdeye85]

"If it B10 is not CC then do your original logic."

How does the formula you created know that though? Is it the order in which you placed the equations,

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

I suppose if you did this, it would not work:

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

Is there an order of operations from excels point of view?

 

[Alex Blakenburg]

See if this helps.

Excel IF Function and IF Example

How to use Excel IF function. Simple examples with step by step instructions. When to use other Excel function instead.

www.contextures.com

 

[thirdeye85]

Thank you so much Alex for all your help!