Need an If formula

fedcco

New Member
Joined
Aug 14, 2012
Messages
22
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.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 14, 2012
Messages
22
Date Tenant Paid
Mgmt. Fees Col'd.Repairs Amt.Repairs Chk. No.Late Fee Col'd.Late Fee RCRLate Fee OwnerBal. Due Owner
03/13/19 140140140.00
0 0.00

<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> </tbody>


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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 14, 2012
Messages
22

ADVERTISEMENT

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 14, 2012
Messages
22

ADVERTISEMENT

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

A1
B1
C1
D1
E1
F1
G1
H1
I1
J1
K1
L1
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

01/01/19
01/12/19
$100
$55.12

$130
FALSE
$130
7
Feb
$1,200


02/01/19
02/07/19
$100


$80
$80
FALSE
8
Mar
$1,200


03/01/19
03/02/19
$100


$0
$0
FALSE
9
Apr
$1,200


04/01/19
04/05/19
$100


$60
$60
FALSE
<tbody> </tbody>

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

 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,473
Office Version
  1. 2016
Platform
  1. Windows
Hi,

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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Mo.</td><td style=";">Rental Amount</td><td style=";">Pmt. Meth</td><td style=";">Pmt. Meth. Comments</td><td style=";">Due Date</td><td style=";">Date Tenant Paid</td><td style=";">Mgmt. Fees Coll'd.</td><td style=";">Repairs Amt.</td><td style=";">Repairs Chk. No.</td><td style=";">Late Fee Coll'd.</td><td style=";">Late Fee RCR</td><td style=";">Late Fee Owner</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Jan</td><td style="text-align: right;;">$1,200 </td><td style=";">Cash</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/1/2019</td><td style="text-align: right;;">1/12/2019</td><td style="text-align: right;;">$100 </td><td style="text-align: right;;">$55.12 </td><td style="text-align: right;;"></td><td style="text-align: right;;">$130 </td><td style="text-align: right;;">$0</td><td style="text-align: right;;">$130</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Feb</td><td style="text-align: right;;">$1,200 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2/1/2019</td><td style="text-align: right;;">2/7/2019</td><td style="text-align: right;;">$100 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">$80 </td><td style="text-align: right;;">$80</td><td style="text-align: right;;">$0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Mar</td><td style="text-align: right;;">$1,200 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3/1/2019</td><td style="text-align: right;;">3/2/2019</td><td style="text-align: right;;">$100 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">$0 </td><td style="text-align: right;;">$0</td><td style="text-align: right;;">$0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Apr</td><td style="text-align: right;;">$1,200 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">4/1/2019</td><td style="text-align: right;;">4/5/2019</td><td style="text-align: right;;">$100 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">$60 </td><td style="text-align: right;;">$60</td><td style="text-align: right;;">$0</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet571</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K6</th><td style="text-align:left">=IF(<font color="Blue">J6<=100,J6,0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L6</th><td style="text-align:left">=IF(<font color="Blue">J6>100,J6,0</font>)</td></tr></tbody></table></td></tr></table><br />
 

fedcco

New Member
Joined
Aug 14, 2012
Messages
22
Thanks to all who helped. Everything is right with the world.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,473
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, glad we can help.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,321
Messages
5,600,948
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top