IF AND OR Query.

fintail99

New Member
Joined
Apr 4, 2017
Messages
38
Hi there,

I wondered if you could assist with the following:

Value
In cell G4, I have a formula which automatically determines & returns a monetary amount, eg £10.00, based upon hours calculated in another cell

Type
In cell H4, I wish to manually enter either “cash” or “bacs”

Attended
In cell I4, I wish to manually enter either “yes” or “no”

Due BACS
In cell J4, I have a formula which automatically enters the monetary amount from G4, subject to H4 being “bacs” AND I4 being “yes”. Else, the cell would remain blank

Due Cash
In cell K4, I have a formula which automatically enters the monetary amount from G4, subject to H4 being “cash” AND I4 being “yes”. Else, the cell would remain blank

Paid BACS
In cell L4, I wish to manually enter the monetary amount that has been paid (usually correlates to the amount in G4), or leave it blank

Paid Cash
In cell M4, I wish to manually enter the monetary amount that has been paid (usually correlates to the amount in G4), or leave it blank

All of the above works well.

However, in cell O4, I require a formula that allows O4 to remain blank unless either J4 or K4 comprise an amount. If J4 or K4 comprise an amount, I require O4 to show a negative amount, eg -£10.00, to identify that an amount is due, but remains unpaid.

Then, once an amount is manually entered into either L4 or M4 (assuming £10.00 is entered), cell O4 should then show £0.00 (or calculate how much remains underpaid/overpaid if a different amount is entered in L4 or M4).

Any help would be appreciated.

Kind regards,
Ketan
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,632
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=IF(AND(J4=""&K4=""),"",SUM(L4:M4)+SUM(J4:K4)*-1)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,632
Office Version
  1. 365
Platform
  1. Windows
My pleasure & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,787
Messages
5,626,876
Members
416,208
Latest member
tan21

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