Excel Help Please?

I'm building a business case model for a customer

I've simplified it to this example for the purposes of asking this question.

I have a spreadsheet that shows costs hitting a budget



Lets say I'm going to buy a car for £10,000 in Q1 of 2012, and pay for it over 12 months in four quarterly payments of £2,500 each.

So I fill in the figures in yellow and I want Excel to automatically calculate the figures in blue.

So quarters 1 to 4 are £0.00, quarters 5 to 8 are £2,500, and quarters 9 onwards are back to zero

Start paying in Quarter 5 referenced by cell D4

Stop paying in Quarter 9 reference by (C4/3)+D4

each blue cell needs to reference row 2 so it knows which quarter it is calculating for

What formula do I need in each blue cell?

I'm thinking some kind of nested IF statement, or maybe a LOOKUP, but my brain is fried and I can't figure it out.

[andrewsmith]

Ralph
Your right that it'll be a an IF statement, or that is how I would do it

*toddles off to excel*

Thanks

Yeah, so in plain English cell J4 needs to say

Return £0.00, unless D4 equal to or greater than J2, in which case return E4; unless J2 equal to or greater than (C4/3)+D4, in which case go back to returning £0.00

Will need persistent $ signs for J$2, and $C4, $D4 and $E4

[andrewsmith]

The $ sign is the format issue change the currency sign would cure that

Ralph I've got a short thread running from last week about IF statements could be worth putting a call for help there

http://forums.sv650.org/showthread.php?t=168153

Originally Posted by -Ralph-

Thanks

Yeah, so in plain English cell J4 needs to say

Return £0.00, unless D4 equal to or greater than J2, in which case return E4; unless J2 equal to or greater than (C4/3)+D4, in which case go back to returning £0.00

Will need persistent $ signs for J$2, and $C4, $D4 and $E4

Something like =IF(($D4=>$J2),$E4,IF($J2=>(($C4/3)+$D4),0) making sure the cell format is set to output £X.XX.
Not sure if you might need a SUM between IF($J2=>(SUM($C4/3)+$D4), it's been a while.

I haven't got it installed on this pc but I might get a min at lunch/work Tues to have a pop.

Please let us know if you cracked it.

I've got a bunch of guys that programme excel for fun. They are not right in the head, but they are good.
If I see this conundrum isn't solved tomorrow, I'll grab one by the ear and tell him to solve it for you.

This is not an open offer to orgers for Excel support, I don't like dealing with the geeks, they smell funny.

[andrewsmith]

look like this Idle Biker?

Too smart, but the white socks and blacks shoes are very similar....

Where did you get hold of my holiday snaps?

Originally Posted by Fruity-ya-ya

Something like =IF(($D4=>$J2),$E4,IF($J2=>(($C4/3)+$D4),0) making sure the cell format is set to output £X.XX.
Not sure if you might need a SUM between IF($J2=>(SUM($C4/3)+$D4), it's been a while.

I haven't got it installed on this pc but I might get a min at lunch/work Tues to have a pop.

Please let us know if you cracked it.

Thanks for the help guys, appreciated

Tried that first one Fruity before giving up and posting this thread, the SUM argument doesn't change it. Returns the right result in 2012, but also returns 2500 in 2011, and FALSE in 2012

Is there any way I can simplify it by combining an IF statement and an AND statement?

IF ('row 2 is equal to or greater than 5' AND 'row 2 is less than 9'), return 2500, if not return zero