Formula help

K

kstory

New Member
Joined
Apr 9, 2018
Messages
3
I am trying to write a formula for the following:

if(and(d3="normal credit",d3="loans-gen credit"),-e3,
if(and(d3="normal debit",d3="loans-gen debit"),+e3

I get the end result of FALSE.

Is there another formula solution for multiple questions?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
kstory said:
I am trying to write a formula for the following:

if(and(d3="normal credit",d3="loans-gen credit"),-e3,
if(and(d3="normal debit",d3="loans-gen debit"),+e3

I get the end result of FALSE.

Is there another formula solution for multiple questions?
Click to expand...

Welcome to the board. Your formula implies that D3 contains both of those values at the same time. Is that the case? Can you provide a small sample of your data and what you want the formula to do?
 
Upvote 0
No, I see that is an error in my formula. The cell d3 can be any of 4 options - normal debit, normal credit, loans-gen debit or loans-gen credit. If the cell is normal debit or loans-gen debit I need the formal to bring back the positive result in E3 (which will always be a number). If D3 is normal credit or loans-gen credit, I need the result to be -e3 (the negative of e3).
I need the following result:
Normal Credit 352.07 (352.07)
Loans-Gen Debit 3.28 3.28
Loans-Gen Debit 23.89 23.89
Loans-Gen Credit 3,558.95 (3,558.95)
Loans-Gen Credit 401.58 (401.58)
Normal Debit 103,537.78 103,537.78
Normal Debit 314,716.94 314,716.94
Normal Credit 134,282.16 (134,282.16)
<colgroup><col width="114" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4169;"> <col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <tbody> </tbody>
 
Upvote 0
Hi,

Looking at your sample, I think you need the OR function rather than AND.
If you have Other possible terms in D3, use formula in F3.
If the Key words are "Credit", and "Debit", you can use the formula in F6.
If there are no Other possible terms in D3 Other than ?Credit, and ?Debit, you can use the formula in F9.


Excel 2010
DEF
3Normal Credit100100
4Normal Debit200-200
5Service Charge10
6Loans-Gen Credit300300
7Loans-Gen Debit400-400
8Monthly Fee20
9Normal Credit500500
10Normal Debit600-600
Sheet13
Cell Formulas
RangeFormula
F3=IF(OR(D3={"Normal Credit","Loans-Gen Credit"}),E3,IF(OR(D3={"Normal Debit","Loans-Gen Debit"}),-E3,""))
F6=IF(ISNUMBER(SEARCH("Credit",D6)),E6,IF(ISNUMBER(SEARCH("Debit",D6)),-E6,""))
F9=IF(OR(D9={"Normal Credit","Loans-Gen Credit"}),E9,-E9)
 
Upvote 0
I actually just now read your Post #3 , looks like you want the logic the other way, so just a small tweak as follows:

Again:
Looking at your sample, I think you need the OR function rather than AND.
If you have Other possible terms in D3, use formula in F3.
If the Key words are "Credit", and "Debit", you can use the formula in F6.
If there are no Other possible terms in D3 Other than ?Credit, and ?Debit, you can use the formula in F9.


Excel 2010
DEF
3Normal Credit100-100
4Normal Debit200200
5Service Charge10
6Loans-Gen Credit300-300
7Loans-Gen Debit400400
8Monthly Fee20
9Normal Credit500-500
10Normal Debit600600
Sheet13
Cell Formulas
RangeFormula
F3=IF(OR(D3={"Normal Credit","Loans-Gen Credit"}),-E3,IF(OR(D3={"Normal Debit","Loans-Gen Debit"}),E3,""))
F6=IF(ISNUMBER(SEARCH("Credit",D6)),-E6,IF(ISNUMBER(SEARCH("Debit",D6)),E6,""))
F9=IF(OR(D9={"Normal Credit","Loans-Gen Credit"}),-E9,E9)
 
Upvote 0
kstory said:
I am trying to write a formula for the following:
if(and(d3="normal credit",d3="loans-gen credit"),-e3,
if(and(d3="normal debit",d3="loans-gen debit"),+e3
I get the end result of FALSE.
Is there another formula solution for multiple questions?

No, I see that is an error in my formula. The cell d3 can be any of 4 options - normal debit, normal credit, loans-gen debit or loans-gen credit. If the cell is normal debit or loans-gen debit I need the formal to bring back the positive result in E3 (which will always be a number). If D3 is normal credit or loans-gen credit, I need the result to be -e3 (the negative of e3).

​



I need the following result:
Normal Credit352.07 (352.07)
Loans-Gen Debit3.28 3.28
Loans-Gen Debit23.89 23.89
Loans-Gen Credit3,558.95 (3,558.95)
Loans-Gen Credit401.58 (401.58)
Normal Debit103,537.78 103,537.78
Normal Debit314,716.94 314,716.94
Normal Credit134,282.16 (134,282.16)

<tbody>
</tbody>
Click to expand...
<strike></strike>
​
Hi!

Try the formulas below in F2 and copy down:

=IFERROR(-(FIND("Credit",D2)>0),1)*E2

Or

=IFERROR(-(SEARCH("Credit",D2)>0),1)*E2


ABCDEFG
1Col DCol ECol F
2Normal Credit352,07 -352,07
3Loans-Gen Debit3,28 3,28
4Loans-Gen Debit23,89 23,89
5Loans-Gen Credit3.558,95 -3.558,95
6Loans-Gen Credit401,58 -401,58
7Normal Debit103.537,78 103.537,78
8Normal Debit314.716,94 314.716,94
9Normal Credit134.282,16 -134.282,16
10
**************************************************

<tbody>
</tbody>


Markmzz
 
Last edited:
Upvote 0
jtakw said:
Hi,

Looking at your sample, I think you need the OR function rather than AND.
If you have Other possible terms in D3, use formula in F3.
If the Key words are "Credit", and "Debit", you can use the formula in F6.
If there are no Other possible terms in D3 Other than ?Credit, and ?Debit, you can use the formula in F9.

Excel 2010
DEF
3Normal Credit100100
4Normal Debit200-200
5Service Charge10
6Loans-Gen Credit300300
7Loans-Gen Debit400-400
8Monthly Fee20
9Normal Credit500500
10Normal Debit600-600

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet13​

Worksheet Formulas
CellFormula
F3=IF(OR(D3={"Normal Credit","Loans-Gen Credit"}),E3,IF(OR(D3={"Normal Debit","Loans-Gen Debit"}),-E3,""))
F6=IF(ISNUMBER(SEARCH("Credit",D6)),E6,IF(ISNUMBER(SEARCH("Debit",D6)),-E6,""))
F9=IF(OR(D9={"Normal Credit","Loans-Gen Credit"}),E9,-E9)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
Click to expand...
Thank you!!!
 
Upvote 0
You're welcome, welcome to the forum.
 
Upvote 0
Hi!
​

A new version in F2 and copy down:

=IFERROR(LOOKUP(8^9,FIND({"Credit";"Debit"},D2),{-1;1})*E2,"")

Or

=IFERROR(LOOKUP(8^9,SEARCH({"Credit";"Debit"},D2),{-1;1})*E2,"")

Markmzz
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
sometimes problem with vba (Error 400)
Replies
5
Views
154
Domenic
D
HappyChappy
  • Solved
tidying up formulas
Replies
4
Views
142
HappyChappy
HappyChappy
L
  • Question
Excel VBA - Return Specific Data from Master Sheet Based on Date Search + Duplicate Certain Items
Replies
1
Views
446
LookingForHelp1
L
T
  • Solved
Nesting in the FILTER function
Replies
4
Views
287
Eric W
Eric W
T
  • Question
Sum of Cells based on Cell color format
2
Replies
10
Views
484
awoohaw
awoohaw

Forum statistics

Threads
1,215,025
Messages
6,122,732
Members
449,093
Latest member
Mnur

Share this page

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
Back
Top