Need an If formula

[fedcco]

I need an Excel formula that will calculate the following: If J6 is greater than 100, then it will calculate the amount in L6 but if it is equal to or less than 100, then it will calculate the amount in K6 and J6 will show a zero and vice versa.

 

[Joe4]

Formulas cannot return values to other cells, only the cells that they are located in.
So, you would just write formulas in each of the cells where the results might end up that either return the calculation if the conditions are met, or returns a blank.

For example, let's say that if A1 > 10, you want "Yes" to appear in B1. Otherwise, you want "No" to appear in C1.
Then, you would place this formula in cell B1:
=IF(A1>10,"Yes","")
and this formula in C1:
=IF(A1<=10,"No","")

 

[fedcco]

[TABLE="width: 322"]
<colgroup><col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"> <col width="54" style="width: 41pt; mso-width-source: userset; mso-width-alt: 1974;"> <col width="55" style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;"> <col width="55" style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;"> <col width="44" style="width: 33pt; mso-width-source: userset; mso-width-alt: 1609;" span="2"> <col width="44" style="width: 33pt; mso-width-source: userset; mso-width-alt: 1609;"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;"> <tbody>[TR]
[TD="width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Date Tenant Paid
[/TD]
[TD="width: 54, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Mgmt. Fees Col'd.[/TD]
[TD="width: 55, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Repairs Amt.[/TD]
[TD="width: 55, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Repairs Chk. No.[/TD]
[TD="width: 44, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Late Fee Col'd.[/TD]
[TD="width: 44, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Late Fee RCR[/TD]
[TD="width: 44, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Late Fee Owner[/TD]
[TD="width: 62, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "]Bal. Due Owner[/TD]
[/TR]
[TR]
[TD="width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]03/13/19[/TD]
[TD="width: 54, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "] [/TD]
[TD="width: 55, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "] [/TD]
[TD="width: 55, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]140[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]140[/TD]
[TD="width: 62, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]140.00[/TD]
[/TR]
[TR]
[TD="width: 71, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "] [/TD]
[TD="width: 54, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "] [/TD]
[TD="width: 55, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "] [/TD]
[TD="width: 55, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] "] [/TD]
[TD="width: 62, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DDEBF7]#DDEBF7[/URL] , align: right"]0.00

[/TD]
[/TR]
</tbody>[/TABLE]


Late fee Col'd is J6 Which gets the amount from another formula in J6. Late fee to RCR is K6 which is the (<=) formula that I need from the amount in J6. Late fee Owner is L6 which is the (>) formula that I need from J6 also. I was am using (=IF(j6>100,j6) which gives me the correct amount; in this example 140 and if under or less than 100 it gives me "False". I copied and pasted your formulas, changed the cells accordingly but they don't perform the calculations as needed. Also if L6 returns a false instead of an amount, I'd like for it to show a "0" and the same for K6.

 

[Joe4]

Late fee Col'd is J6 Which gets the amount from another formula in J6

I do not understand this. This does not make sense. How can the amount in cell J6 get the amount from another formula in J6?
You can only have one formula or hard-coded value in a cell at a time. J6 cannot contain two different things. It is either one formula or one hard-coded amount.

 

[fedcco]

My apologies; J6 gets the amounts from (E6 Due Date) and (F6 Date Paid) not shown in sample.

 

[Joe4]

OK, so can you walk us through an actual example again, where you tell us what hard-coded values are in what cells on row 6, and what the criteria for the calculations in the other cells can be (while walking us through the enitre example so we can see the data and logic behind it all. It is still a little fuzzy based on your first attempt, but you had some errors there and didn't include everything.

 

[fedcco]

So the scenario is as follows: J6 has this formula [=((F6-E6)>=3)*50+((F6-E6)>=4)*(F6-E6-3)*10)]which subtracts the dates from E6 & F6and figures that amounts on J6. Ijust need the formulas for K6 and L6 so that if the late fee is $100 orless (say $80 as is on J7), they go on column K and if more than $100, (say $130 as is on J6) they go on column L and instead of showing “FALSE” as on K6, the cell shows “0”

[TABLE="width: 523"]
<tbody>[TR]
[TD="width: 35"]

​

[/TD]
[TD="width: 37"]

A1​

[/TD]
[TD="width: 59"]

B1​

[/TD]
[TD="width: 45"]

C1​

[/TD]
[TD="width: 74"]

D1​

[/TD]
[TD="width: 66"]

E1​

[/TD]
[TD="width: 66"]

F1​

[/TD]
[TD="width: 51"]

G1​

[/TD]
[TD="width: 56"]

H1​

[/TD]
[TD="width: 56"]

I1​

[/TD]
[TD="width: 49"]

J1​

[/TD]
[TD="width: 51"]

K1​

[/TD]
[TD="width: 52"]

L1​

[/TD]
[/TR]
[TR]
[TD="width: 35"]

​

[/TD]
[TD="width: 37"]

Mo.​

[/TD]
[TD="width: 59"]

Rental Amount​

[/TD]
[TD="width: 45"]

Pmt. Meth​

[/TD]
[TD="width: 74"]

Pmt. Meth. Comments​

[/TD]
[TD="width: 66"]

Due Date​

[/TD]
[TD="width: 66"]

Date Tenant Paid​

[/TD]
[TD="width: 51"]

Mgmt. Fees Coll'd.​

[/TD]
[TD="width: 56"]

Repairs Amt.​

[/TD]
[TD="width: 56"]

Repairs Chk. No.​

[/TD]
[TD="width: 49"]

Late Fee Coll'd.​

[/TD]
[TD="width: 51"]

Late Fee RCR​

[/TD]
[TD="width: 52"]

Late Fee Owner​

[/TD]
[/TR]
[TR]
[TD="width: 35"] 6
[/TD]
[TD="width: 37"] Jan
[/TD]
[TD="width: 59"]

$1,200​

[/TD]
[TD="width: 45"] Cash
[/TD]
[TD="width: 74"]
[/TD]
[TD="width: 66"]

01/01/19​

[/TD]
[TD="width: 66"]

01/12/19​

[/TD]
[TD="width: 51"]

$100​

[/TD]
[TD="width: 56"]

$55.12​

[/TD]
[TD="width: 56"]
[/TD]
[TD="width: 49"]

$130​

[/TD]
[TD="width: 51"]

FALSE​

[/TD]
[TD="width: 52"]

$130​

[/TD]
[/TR]
[TR]
[TD="width: 35"] 7
[/TD]
[TD="width: 37"] Feb
[/TD]
[TD="width: 59"]

$1,200​

[/TD]
[TD="width: 45"]
[/TD]
[TD="width: 74"]
[/TD]
[TD="width: 66"]

02/01/19​

[/TD]
[TD="width: 66"]

02/07/19​

[/TD]
[TD="width: 51"]

$100​

[/TD]
[TD="width: 56"]
[/TD]
[TD="width: 56"]
[/TD]
[TD="width: 49"]

$80​

[/TD]
[TD="width: 51"]

$80​

[/TD]
[TD="width: 52"]

FALSE​

[/TD]
[/TR]
[TR]
[TD="width: 35"] 8
[/TD]
[TD="width: 37"] Mar
[/TD]
[TD="width: 59"]

$1,200​

[/TD]
[TD="width: 45"]
[/TD]
[TD="width: 74"]
[/TD]
[TD="width: 66"]

03/01/19​

[/TD]
[TD="width: 66"]

03/02/19​

[/TD]
[TD="width: 51"]

$100​

[/TD]
[TD="width: 56"]
[/TD]
[TD="width: 56"]
[/TD]
[TD="width: 49"]

$0​

[/TD]
[TD="width: 51"]

$0​

[/TD]
[TD="width: 52"]

FALSE​

[/TD]
[/TR]
[TR]
[TD="width: 35"] 9
[/TD]
[TD="width: 37"] Apr
[/TD]
[TD="width: 59"]

$1,200​

[/TD]
[TD="width: 45"]
[/TD]
[TD="width: 74"]
[/TD]
[TD="width: 66"]

04/01/19​

[/TD]
[TD="width: 66"]

04/05/19​

[/TD]
[TD="width: 51"]

$100​

[/TD]
[TD="width: 56"]
[/TD]
[TD="width: 56"]
[/TD]
[TD="width: 49"]

$60​

[/TD]
[TD="width: 51"]

$60​

[/TD]
[TD="width: 52"]

FALSE​

[/TD]
[/TR]
</tbody>[/TABLE]

Hope this clarifies my needs. And thanks for the help.

 

[jtakw]

Hi,

All you really needed to do is to modify Joe4's suggestions in Post # 2 to suit your requirements:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
5	Mo.	Rental Amount	Pmt. Meth	Pmt. Meth. Comments	Due Date	Date Tenant Paid	Mgmt. Fees Coll'd.	Repairs Amt.	Repairs Chk. No.	Late Fee Coll'd.	Late Fee RCR	Late Fee Owner
6	Jan	$1,200	Cash		1/1/2019	1/12/2019	$100	$55.12		$130	$0	$130
7	Feb	$1,200			2/1/2019	2/7/2019	$100			$80	$80	$0
8	Mar	$1,200			3/1/2019	3/2/2019	$100			$0	$0	$0
9	Apr	$1,200			4/1/2019	4/5/2019	$100			$60	$60	$0
Sheet571

Cell Formulas
Range	Formula
K6	=IF(J6<=100,J6,0)
L6	=IF(J6>100,J6,0)

 

[fedcco]

Thanks to all who helped. Everything is right with the world.

 

[jtakw]

You're welcome, glad we can help.