If condition: help needed

[lome53]

Hi everyone,

I am a voluntary treasurer of a local charity and I am building an excel model to capture members subscriptions and their additional donation.

The model I have has three tabs; Payments, Subscriptions and donations. Members who are singles contribute £5 a month and couples contribute £10, but sometimes members contribute less than their monthly subscriptions. However, sometimes members pay their subscriptions together with donations. I want a formula in the subscriptions tab that is able to split the subscription amount from donation and ensure that the amount for a couple or single is entered even if the amount paid is less than the required subscription.

E.g. a member who is single paid £35 in a month. This is entered against the member in the payments tab. I want a formula in the subscriptions tab to take only £5 as subscriptions against that member and the donations tab to take the rest. Where a member pays less than the required subscription, the full amount is entered as subscriptions.

I have tried and cannot figure out what formula I need. Below is the formula I put in Subscriptions tab but is giving me £5 even if the subscription is paid by a couple.

=IF(AND(E6="Single",'2018 Payments'!G6<=5),'2018 Payments'!G6,IF('2018 Payments'!G6>5,5,IF(AND(E6="",'2018 Payments'!G6<=5),'2018 Payments'!G6,IF('2018 Payments'!G6>5,5,IF(AND(E6="couple",'2018 Payments'!G6<=10),'2018 Payments'!G6,IF('2018 Payments'!G6>10,5,0))))))

Where column E identifies the member as being single or couple


Your help in this will be greatly appreciated.

Regards

Lome

[DocAElstein]

Hi lome,

I am not sure if I have the logic correct of what you want.
I find it helpful to put theses things clearly into words
This is what I understand…
A formula is wanted in a cell subscriptions worksheet
A payment is made in cell G6 worksheet payment from a "single" or "couple" member, ( - 35 for a Month was your example - cell G6 in worksheet payment has 35 put in it ). It could come from a single or couple Member.
If single or couple is shown:
In subscriptions worksheet column E - ( E6 in the example ) I may have “single” or “couple” .
Then your logic.. The model I have has three tabs; Payments, Subscriptions and donations. Members who are singles contribute £5 a month and couples contribute £10, but sometimes members contribute less than their monthly subscriptions. However, sometimes members pay their subscriptions together with donations. I want a formula in the subscriptions tab that is able to split the subscription amount from donation and ensure that the amount for a couple or single is entered even if the amount paid is less than the required subscription.
E.g. a member who is single paid £35 in a month. This is entered against the member in the payments tab. I want a formula in the subscriptions tab to take only £5 as subscriptions against that member and the donations tab to take the rest. Where a member pays less than the required subscription, the full amount is entered as subscriptions.
I only need to check for single: If I do not have single then I assume I have couple.

So my logic:
If ( Single then ; ____ ; Else _____ )
____ will be case single
_____ will be case couple

____ and _____ will be very similar according to your logic, differing only by
____ having a 5 in it
_____ having a 10 in it

=IF(E6="single",IF(Payments!G6<=5,Payments!G6,5),IF(Payments!G6<=10,Payments!G6,10))

Worksheet: Subscriptions

If I have not got it right, then can you upload two workbooks, with just a small amount of data. Desensitise the data ( make names up etc.. but pick the data carefully so that it demonstrates typical scenarios. )

One file should be the “Before” - so what you have before you enter any payment.

The “after” should be hand filled in by you to show what you want the formula to do, that is to say what results you want to have. And say what you will be wanting to be doing to get those results.

Alan

[lome53]

Hi lome,

I am not sure if I have the logic correct of what you want.
I find it helpful to put theses things clearly into words
This is what I understand…
A formula is wanted in subscriptions
In subscriptions column E I may have “single” or “couple” .
I only need to check for single: If I do not have single then I assume I have couple.

So my logic:
If Single then ____ ; Else _____
____ will be case single
_____ will be case couple

____ and _____ will be very similar, differing only by
____ having a 5 in it
_____ having a 10 in it

=IF(E6="single",IF(Payments!G6<=5,Payments!G6,5),IF(Payments!G6<=10,Payments!G6,10))

Worksheet: Subscriptions

If I have not got it right, then can you upload two workbooks, with just a small amount of data. Desensitise the data ( make names up etc.. but pick the data carefully so that it demonstrates typical scenarios. )

One file should be the “Before” - so what you have before you enter any payment.

The “after” should be hand filled in by you to show what you want the formula to do, that is to say what results you want to have. And say what you will be wanting to be doing to get those results.

Alan

Alan,

Thanks for looking at this post. I forgot to mention that when a member's status is not know, column E is left blank and the assumption in subscriptions is that the member is single. So you can have single, couple or blank in column E, but when blank, the formula in subscriptions assumes single and allocate £5. Sample Model.xlsx

I used insert Image as I cannot see an option to upload file

In the file:

Row4 where there was no payment
Row5 where member is a couple and paid subs with donation
Row6 where member is single and paid less than required subs

Thanks once again

[DocAElstein]

Hi

To cover the situation of Single or blank meaning the same then the formula would be

=IF(OR(E6="single",E6=""),IF(Payments!G6<=5,Payments!G6,5),If(Payments!G6<=10,Payments!G6,10))

Worksheet: Subscriptions

I think the following adaptation of that formula to your supplied data seems to give the result you wanted in the January column in Subs.

So this is your Payments

Row\Col	E	F	G	H
1			£29	£0
2
3	Status	Subs Due	Jan	Feb
4	Single	£60
5	Couple	£120	£25
6	Single	£60	£4
7
8

Worksheet: 2018 Payments

Here is the Subs worksheet:

Row\Col	E	F	G	H
1			£14	£0
2
3	Status	Subs Due	Jan	Feb
4	Single	£60	£0
5	Couple	£120	£10
6	Single	£60	£4
7

Worksheet: 2018 Subs

Row\Col	G
4	=IF(OR(E4="single",E4=""),IF('2018 Payments'!G4<=5,'2018 Payments'!G4,5),IF('2018 Payments'!G4<=10,'2018 Payments'!G4,10))
5	=IF(OR(E5="single",E5=""),IF('2018 Payments'!G5<=5,'2018 Payments'!G5,5),IF('2018 Payments'!G5<=10,'2018 Payments'!G5,10))
6	=IF(OR(E6="single",E6=""),IF('2018 Payments'!G6<=5,'2018 Payments'!G6,5),IF('2018 Payments'!G6<=10,'2018 Payments'!G6,10))

Worksheet: 2018 Subs

Alan

[lome53]

Hi

To cover the situation of Single or blank meaning the same then the formula would be

=IF(OR(E6="single",E6=""),IF(Payments!G6<=5,Payments!G6,5),IF(Payments!G6<=10,Payments!G6,10))

Worksheet: Subscriptions

I think the following adaptation of that formula to your supplied data seems to give the result you wanted in the January column in Subs.

So this is your Payments

Row\Col	E	F	G	H
1			£29	£0
2
3	Status	Subs Due	Jan	Feb
4	Single	£60
5	Couple	£120	£25
6	Single	£60	£4
7
8

Worksheet: 2018 Payments

Here is the Subs worksheet:

Row\Col	E	F	G	H
1			£14	£0
2
3	Status	Subs Due	Jan	Feb
4	Single	£60	£0
5	Couple	£120	£10
6	Single	£60	£4
7

Worksheet: 2018 Subs

Row\Col	G
4	=IF(OR(E4="single",E4=""),IF('2018 Payments'!G4<=5,'2018 Payments'!G4,5),IF('2018 Payments'!G4<=10,'2018 Payments'!G4,10))
5	=IF(OR(E5="single",E5=""),IF('2018 Payments'!G5<=5,'2018 Payments'!G5,5),IF('2018 Payments'!G5<=10,'2018 Payments'!G5,10))
6	=IF(OR(E6="single",E6=""),IF('2018 Payments'!G6<=5,'2018 Payments'!G6,5),IF('2018 Payments'!G6<=10,'2018 Payments'!G6,10))

Worksheet: 2018 Subs

Alan

Thanks ever so much Alan, this works perfectly fine. I will let the trustees know about your invaluable contribution to the charity record keeping.

Thanks once again

[DocAElstein]

You are welcome,
I am not a formula man myself, I mostly do coding.
This turned out a lot easier than I expected


( Edit: P.s. When you Reply with Quote if you could edit some bits out that helps to make the Thread a bit easier to read. Just include relavant bits, or use just Reply sometimes. Thanks )

Edit P.s.2 Uploading files is not obvious…
_1) You may need to “Go Advanced”
The normal Start point is with the Paper Clip icon at the top of the Editor Window. That icon will be present in the initial Post Editor, but it may not always be available for further Replys. You may need first to “Go Advanced”
GoAdvancedReplyWindow.JPG : GoAdvanced1.JPG
https://imgur.com/1A9qWQM : https://imgur.com/UXBZ4oJ

_2) _3) Hit Paper Clip and Add Files
PaperClip2AddFiles3.JPG
https://imgur.com/vbPQvTr

_4) Select Files
SelectFiles.JPG
https://imgur.com/aqtVTPa

_5) Upload Files
UploadFiles5.JPG
https://imgur.com/pUfmZc7

_6) Hit Done
Done6.JPG
https://imgur.com/kQAwzao

_-.----
You can also get thereabouts with the Manage Attachments option which you will see when youscroll down after hitting “Go Advanced”
ManageAttachments.JPG
https://imgur.com/KxTxRoC

_.------
To delete Files:
DeleteAttachment.JPG
https://imgur.com/STzpq9E

_..
Best is to practice posting here:
http://www.excelfox.com/forum/forumd...p/17-Test-Area
Start a new Thread, with a title such as “Just testing and practicing posting, no reply needed”
You can do anything you want, post anything, Edit, and try again etc… etc..



https://www.youtube.com/@alanelston2330/featured