IF AND OR Query.

fintail99

New Member
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

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
Excel Formula:
=IF(AND(J4=""&K4=""),"",SUM(L4:M4)+SUM(J4:K4)*-1)

fintail99

New Member
Brilliant! Thank you once again Fluff.

Fluff

MrExcel MVP, Moderator
My pleasure & thanks for the feedback.

Replies
0
Views
50
Replies
1
Views
79
Replies
6
Views
185
Replies
3
Views
153
Replies
4
Views
180

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.

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

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