Re: [libreoffice-users] A simple spreadsheet to allocate Gift Aid

At 14:29 26/02/2013 -0800, J H K Mont wrote:
> I am trying to construct a simple spreadsheet 
> for the allocation of Gift Aid Refunds to 
> different charities. My apologies for the layout 
> of the spreadsheet, it changes from the original when I preview it.
>
> To explain:-
> Column A = Envelope number held by a specific 
> person, this could also be a name.
> Column B = The total amount that person gave to the charity
> Column C = cell which confirms that the 
> allocation to specific funds equals the total 
> amount donated, this adds together totals in each fund column.
> Column D = This identifies if the person giving 
> the donation has signed a gift aid form. If the 
> cell is "Y" then the Gift Aid Refund allocation 
> can be added to the individual funds. If the 
> cell indicates "N" then the Gift aid form has 
> not been signed therefore there will be no gift aid allocation.
> Cell D2 shows the Gift Aid Refund rate claimable.
> Columns E, G, etc. shows how the donor has asked 
> for his donations to be allocated within the charity
> Columns F, H, etc.. shows the amount in Gift Aid 
> refunds should be allocated to each fund.
> In the example below you will see that envelope 
> 1 has signed a Gift Aid form and therefore his 
> donations to funds 1 & 2 are increased by £12.50 
> each. Env. 2 has not signed the Gift Aid form, 
> therefore no Gift Aid can be claimed.
>
> Could someone please give me the formula to use 
> to identify the amount to allocate, in cell F4 I 
> have inserted the amount to allocated from Gift 
> Aid without the information in D4.
>
>                 Gift Aid 
> Refund          25.00%             Fund 1                Fund 2
> Env No.  Donation  Total Allocated  Gift 
> Aid?  Fund 1  Gift Aid  Fund 2  Gift Aid
> 1            £100.00        £100.00           Y 
>       £50.00   £12.50  £50.00   £12.50
> 2            £125.00        £125.00           N 
>      £100.00              £25.00

I'm having to assume that the value you have in 
D2 is a proper percentage, i.e. the value 0.25 
formatted as Percent, and not, say, a text string.

In F4, enter
=IF(D4="Y";E4*D$2;"")
In H4, enter:
=IF(D4="Y";G4*D$2;"")
In each case, fill these formulae down the column.

I trust this helps.

Brian Barker


--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted