Excel Help Please?

[SoulKiss]

2 IFs sort it out - download it here

http://www.chaoscrypt.com/files/xls/Depricalc.xls

You can change any of the blue values, just leave the greys and blues alone

The Quarterly Hit is calculated by dividing the cost by the Term/3

This is the content of the N4 (bottom right cell)

=IF(N1>=C4,(IF(A4-SUM(E4:M4)>0,D4,0)),0)

Originally Posted by -Ralph-

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

I used a SUM to calculate the amount of depreciation already rather than a greater than or less than the quarter index to limit where the amount is dropped to 0

Originally Posted by SoulKiss

2 IFs sort it out - download it here

http://www.chaoscrypt.com/files/xls/Depricalc.xls

You can change any of the blue values, just leave the greys and blues alone

The Quarterly Hit is calculated by dividing the cost by the Term/3

This is the content of the N4 (bottom right cell)

=IF(N1>=C4,(IF(A4-SUM(E4:M4)>0,D4,0)),0)



I used a SUM to calculate the amount of depreciation already rather than a greater than or less than the quarter index to limit where the amount is dropped to 0

Thanks very much.

Have you reached the right month yet, yes? Start calculating. Have you reached £10k yet, yes? Stop calculating then. Keep it simples! Like it!

That now goes into a bigger formula that calculates other stuff in each cell, but I've done those already, that's the bit I couldn't get working.

When a solution is reached, you often realise you were just over complicating it!

Problem solved guys!

[SoulKiss]

Originally Posted by -Ralph-

Thanks very much.

Have you reached the right month yet, yes? Start calculating. Have you reached £10k yet, yes? Stop calculating then. Keep it simples! Like it!

That now goes into a bigger formula that calculates other stuff in each cell, but I've done those already, that's the bit I couldn't get working.

When a solution is reached, you often realise you were just over complicating it!

Problem solved guys!

I am sure that you looked at the other cells and saw that the SUM only goes up to the cell before...

And it actually keeps calculating forever after the start date, its just that the calculation also has a set to zero clause.

Could be prettier but it works

[Sir Trev]

Originally Posted by -Ralph-

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

Ralph/all

I'm no expert at formula creation (I only wear white socks with trainers in the gym and I never carry pens in my shirt pocket) but I have learned some hard lessons over the years regarding layout. In your example you reference a number to correspond to a quarter - this means future-proofing the model is difficult. There is a good chance the model will be replaced before two years is up but after struggling to add periods to models in the past consider how it can be achieved as you set out on this build. It saves a lot of hassle in the long run.

A few years ago I attended a very interesting talk by a guy who founded a company that builds models professionally. There is a real art to it and of course he was pushing his book at the talk. Was over fifty notes so I didn't buy one but his company was absolutely swamped with work.

Originally Posted by Sir Trev

Ralph/all

I'm no expert at formula creation (I only wear white socks with trainers in the gym and I never carry pens in my shirt pocket) but I have learned some hard lessons over the years regarding layout. In your example you reference a number to correspond to a quarter - this means future-proofing the model is difficult. There is a good chance the model will be replaced before two years is up but after struggling to add periods to models in the past consider how it can be achieved as you set out on this build. It saves a lot of hassle in the long run.

A few years ago I attended a very interesting talk by a guy who founded a company that builds models professionally. There is a real art to it and of course he was pushing his book at the talk. Was over fifty notes so I didn't buy one but his company was absolutely swamped with work.

That's not the actual model mate, it was just a simplistic example to demonstrate the problem I was having with figuring out that one calculation. If I had posted the actual model, I'd have confused the hell out of everyone and not got the answer I was seeking. Cost of money, OPex analysis, budget view vs balance sheet view, impact of CAPex on cash, show or hide sunk cost, etc, etc, etc

The real model runs up to 7 years. It does still use a numerical identifier for each quarter, but they are hidden, and it prompts the user to define when quarter 1 is and populates the rest of the periods accordingly. Being used as a one-off tool during a sales engagement, and starting afresh each time, I should never have to scale this one.

I've done a few commercial models now, and the biggest issue I have is making them easy for other people to understand and use, as you say making them scale without compromising the model and ending up with people adding their own blank rows and columns that do nothing, and locking them down so people can't mess with them and overwrite formulas inadvertently compromising the end result (because they will blame the model!).

I do need to get some serious in-depth Excel training at some point, figuring it out as you go along is very efficient way of loosing a lot of time! Hence posting this thread at half ten at night, for something that was actually relatively simple!

Originally Posted by SoulKiss

Could be prettier but it works

Once the cells are locked down and the formulas hidden, only the guy with the password (me!) knows how pretty the formula is anyway

If it works that's all that matters

Thanks again.