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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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).
[TABLE="width: 212"]
<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>[TR]
[TD="width: 114, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 86, bgcolor: transparent"]I need the following result:[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Normal Credit[/TD]
[TD="class: xl63, bgcolor: transparent"] 352.07 [/TD]
[TD="class: xl63, bgcolor: transparent"] (352.07)[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Loans-Gen Debit[/TD]
[TD="class: xl63, bgcolor: transparent"] 3.28 [/TD]
[TD="class: xl63, bgcolor: transparent"] 3.28 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Loans-Gen Debit[/TD]
[TD="class: xl63, bgcolor: transparent"] 23.89 [/TD]
[TD="class: xl63, bgcolor: transparent"] 23.89 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Loans-Gen Credit[/TD]
[TD="class: xl63, bgcolor: transparent"] 3,558.95 [/TD]
[TD="class: xl63, bgcolor: transparent"] (3,558.95)[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Loans-Gen Credit[/TD]
[TD="class: xl63, bgcolor: transparent"] 401.58 [/TD]
[TD="class: xl63, bgcolor: transparent"] (401.58)[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Normal Debit[/TD]
[TD="class: xl63, bgcolor: transparent"] 103,537.78 [/TD]
[TD="class: xl63, bgcolor: transparent"] 103,537.78 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Normal Debit[/TD]
[TD="class: xl63, bgcolor: transparent"] 314,716.94 [/TD]
[TD="class: xl63, bgcolor: transparent"] 314,716.94 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Normal Credit[/TD]
[TD="class: xl63, bgcolor: transparent"] 134,282.16 [/TD]
[TD="class: xl63, bgcolor: transparent"] (134,282.16)[/TD]
[/TR]
</tbody>[/TABLE]
 
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).

​

[TABLE="width: 0"]
<tbody>[TR]
[TD]
[/TD]
[TD="width: 81, bgcolor: transparent"]
[/TD]
[TD="class: xl64, width: 86, bgcolor: transparent"]I need the following result:[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Normal Credit[/TD]
[TD="class: xl63, bgcolor: transparent"]352.07 [/TD]
[TD="class: xl63, bgcolor: transparent"](352.07)[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Loans-Gen Debit[/TD]
[TD="class: xl63, bgcolor: transparent"]3.28 [/TD]
[TD="class: xl63, bgcolor: transparent"]3.28 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Loans-Gen Debit[/TD]
[TD="class: xl63, bgcolor: transparent"]23.89 [/TD]
[TD="class: xl63, bgcolor: transparent"]23.89 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Loans-Gen Credit[/TD]
[TD="class: xl63, bgcolor: transparent"]3,558.95 [/TD]
[TD="class: xl63, bgcolor: transparent"](3,558.95)[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Loans-Gen Credit[/TD]
[TD="class: xl63, bgcolor: transparent"]401.58 [/TD]
[TD="class: xl63, bgcolor: transparent"](401.58)[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Normal Debit[/TD]
[TD="class: xl63, bgcolor: transparent"]103,537.78 [/TD]
[TD="class: xl63, bgcolor: transparent"]103,537.78 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Normal Debit[/TD]
[TD="class: xl63, bgcolor: transparent"]314,716.94 [/TD]
[TD="class: xl63, bgcolor: transparent"]314,716.94 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Normal Credit[/TD]
[TD="class: xl63, bgcolor: transparent"]134,282.16 [/TD]
[TD="class: xl63, bgcolor: transparent"](134,282.16)[/TD]
[/TR]
</tbody>[/TABLE]
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


[TABLE="class: grid, width: 409"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Col D[/TD]
[TD]Col E[/TD]
[TD]Col F[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Normal Credit[/TD]
[TD="align: right"]352,07 [/TD]
[TD="align: right"]-352,07 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Loans-Gen Debit[/TD]
[TD="align: right"]3,28 [/TD]
[TD="align: right"]3,28 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Loans-Gen Debit[/TD]
[TD="align: right"]23,89 [/TD]
[TD="align: right"]23,89 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Loans-Gen Credit[/TD]
[TD="align: right"]3.558,95 [/TD]
[TD="align: right"]-3.558,95 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Loans-Gen Credit[/TD]
[TD="align: right"]401,58 [/TD]
[TD="align: right"]-401,58 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Normal Debit[/TD]
[TD="align: right"]103.537,78 [/TD]
[TD="align: right"]103.537,78 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Normal Debit[/TD]
[TD="align: right"]314.716,94 [/TD]
[TD="align: right"]314.716,94 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Normal Credit[/TD]
[TD="align: right"]134.282,16 [/TD]
[TD="align: right"]-134.282,16 [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]**[/TD]
[TD]****************[/TD]
[TD]***********[/TD]
[TD]************[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


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
Normal Credit
Normal Debit
Service Charge
Loans-Gen Credit
Loans-Gen Debit
Monthly Fee
Normal Credit
Normal Debit

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]200[/TD]
[TD="align: right"]-200[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]400[/TD]
[TD="align: right"]-400[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]20[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]600[/TD]
[TD="align: right"]-600[/TD]

</tbody>
Sheet13​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=IF(OR(D3={"Normal Credit","Loans-Gen Credit"}),E3,IF(OR(D3={"Normal Debit","Loans-Gen Debit"}),-E3,""))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F6[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH("Credit",D6)),E6,IF(ISNUMBER(SEARCH("Debit",D6)),-E6,""))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F9[/TH]
[TD="align: left"]=IF(OR(D9={"Normal Credit","Loans-Gen Credit"}),E9,-E9)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
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

G
  • Solved
Typing out Excel formula in Excel Macro
Replies
1
Views
86
Macropod
Macropod
B
  • Solved
how to use find formula in a countif formula
Replies
5
Views
58
etaf
etaf
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
426
STARL0RD
S
E
  • Question Question
Trial Balance, INDEX/MATCH, Multiple Criteria, and SPILL
Replies
2
Views
483
Cubist
Cubist
J
  • Question Question
Using index and match from another source excel file
2
Replies
10
Views
708
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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