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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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

S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
365
STARL0RD
S
E
  • Question Question
Trial Balance, INDEX/MATCH, Multiple Criteria, and SPILL
Replies
2
Views
448
Cubist
Cubist
J
  • Question Question
Using index and match from another source excel file
2
Replies
10
Views
666
Peter_SSs
Peter_SSs
L
  • Solved
Trouble running an access query in excel vba
Replies
2
Views
155
Legal Tender
L
N
  • Solved
Search to return all variances
Replies
3
Views
190
Fluff
Fluff

Forum statistics

Threads
1,223,908
Messages
6,175,304
Members
452,633
Latest member
DougMo

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