IF, AND, OR Formula

**Loubles** · 06-21-2009, 10:50 AM

Hi,

I have a problem with a spreadsheet I am working on in Excel 2007.

I will try to include as much information as possible but please feel free to contact me if I have missed anything out.

I am trying to compile a spreadsheet for a new contract year in August 09, what I need is this spreadsheet to calculate on programme monthly payments for our learners. I have the following columns in my spreadsheet:

Name
Type (Start, monthly, final)
Banding (A,B or C)
(Type and Band have different financial values, ie. a start for band A could be £1, band B £2 and band C £3, then monthly for Band A £2, Band B £4 and so on)
Start Date
Planned end date
Actual end date
12x Monthly payments (ie. Period 1, period 2, period 3 ....)

What I need is for a calculation to be entered into the monthly payment columns ie. Period 1 - to pick up the type and banding figure for that row(start/band A - £1), if the learner was in learning in August 2009, so the formula needs to look at the start date and then the planned end but then for the actual end date to overide the planned end date if there is a date entered in this column.

I need to be able to see that each row, which will be for each learner gives a total funding for that individual for the contract year, ie. if they start in Period 1 but actual end date is Period 4 then columns Period 1 - 4 will generate a figure for each column.
Also I need to see the total funding for each period (column).

Example: If a leaner starts in August (Period 1) and is due to finish in December (Period 5), but actually leaves in September (Period 2) then the formula only generates a payment for Period (Column) 1-2.

I am fine with sum calculations but have limited experience with IF formulas.

I would appreciated any advice or support anyone could give me on this, if needs be I can add a screen shot if I'm not making myself clear.

Ok, I am goign to stop now, I think I'll probably confuse folks even more if I carry on.

Thanks in anticipation of your support.

**shg** · 06-21-2009, 11:35 AM

Welcome to the forum.

You're more likely to get useful help if you post an example workbook and explain in context.

**Loubles** · 06-21-2009, 12:27 PM

Thanks

I have managed to sort the calculations to work out the type and bandings I just need the formula for linking the Value to the Period columns dependant on the dates entered.

I have entered X in each cell where it should have a value.

I have the flexibility of working in either excel 2003 or 2007.

I have attached the spreadsheet as an attachment, hope this works.

Many thanks

**martindwilson** · 06-21-2009, 01:07 PM

what should be in the cells with an X?

**Loubles** · 06-21-2009, 04:52 PM

The value from column G.

I need the formula for IF to be something like:

IF the start date is before or equal to the first of the month (ie. P1 1st August) then a value needs to show, however, only in each of those periods up until the planned end date, or if the learner leaves before planned date then the actual end date needs to over ride the planned.

I have entered the start and end dates on sheet 3 for each period if that helps, although I know that the formula will be different for each period as it needs to refer to a different set of dates, I just need an idea to get started.

Does this make sense?

Thanks again.

**squiggler47** · 06-21-2009, 04:59 PM

How about this

**martindwilson** · 06-21-2009, 06:07 PM

i dont think op wants X but value if you always put end date in J even if its the same as planned then you can use
in k3 across and down
=IF($J3="","",IF(MATCH($J3,$K$1:$V$1,1)=COLUMNS($K$1:K1),$G3,""))
with start of months in k1 thro v1 as squiggler suggested
or if you want j to have blanks
=IF($J3="",IF(MATCH($I3,$K$1:$V$1,1)=COLUMNS($K$1:K1),$G3,""),IF(MATCH($J3,$K$1:$V$1,1)=COLUMNS($K$1:K1),$G3,""))
but if date is greater than last month in row k1:v1 v3 will always end up with the value from g

**Loubles** · 06-22-2009, 03:08 AM

Thats great thanks, I think I have what I need for now, let me play around with it and I'll be back if I have any more queries with this.

Once again thank you!