Calc cost pro rata (nested IF's and macro to find empty row)

------------------------------------------------------------------------
A poll associated with this post was created, to vote and see the
results, please visit http://forums.yourdomain.com.au/showthread.php?t=92356
------------------------------------------------------------------------
Question: What do you think?

- Wow
- Very Good
- OK
- Could be better
- Poor
------------------------------------------------------------------------

Hi all,

I was helped out earlier on the General Discussion forum so I thought
I'd upload my greatest excel achievment!

At work we had a simple calculator to work out the cost of a product
over a date range and calculate it acurately on a per day basis. I
wanted to improve upon this so that only a from and to date had to be
entered and I have done, plus it calculates many products at once.

There are also macros now to paste lines into another workbook which
can work out the next empty row. Using a counter wasn't an option as
some lines would be added manually.

There are obvious limitations with this due to IF. Also if the price of
a product chages during the period you are calculating so have to use
the Original sheet. If anyone can make my meager contribution more
ellagant then please feel free to assimilate and regurgetate! I've
really go to work on the presentation of this as well.

Most of the formulas are hidden below and to the right of the visable
sheets, but there are other hidden sheets you may want to look at. The
main formulas are;

To work out start month for use in CONCATENATE:
=IF(C10<>"",A10,IF(C9<>"",A9,IF(C8<>"",A8,IF(C7<>"",A7,IF(C6<>"",A6,IF(C5<>"",A5,IF(C4<>"",A4)))))))

To work out first broken period rental:
=ROUND(IF(A4>=Original!I4,C4/Original!L4*(Original!J4+1-A4),IF(A4>=Original!I5,C4/Original!L5*(Original!J5+1-A4),IF(A4>=Original!I6,C4/Original!L6*(Original!J6+1-A4),IF(A4>=Original!I7,C4/Original!L7*(Original!J7+1-A4),IF(A4>=Original!I8,C4/Original!L8*(Original!J8+1-A4),IF(A4>=Original!I9,C4/Original!L9*(Original!J9+1-A4),IF(A4>=Original!I10,C4/Original!L10*(Original!J10+1-A4),""))))))),2)

To work our whole month rentals:
=IF(A4>=Original!I6,Original!M5,IF(A4>=Original!I7,Original!M6,IF(A4>=Original!I8,Original!M7,IF(A4>=Original!I9,Original!M8,IF(A4>=Original!I10,Original!M9,IF(A4>=Original!I11,Original!M10,IF(A4>=Original!I12,Original!M11)))))))
=IF(B4>Original!J6,Original!M5,IF(B4>Original!J7,Original!M6,IF(B4>Original!J8,Original!M7,IF(B4>Original!J9,Original!M8,IF(B4>Original!J10,Original!M9,IF(B4>Original!J11,Original!M10,IF(B4>Original!J12,Original!M11,8)))))))
=C4*(E42-E43)
C4 is the cost and E42+43 are the values returned from the above


After all that I can't upload the rental calculator, only room for the
bulk calculator. Oh well the formula I used to get the next free row
was:

Application.Goto Reference:="R7C1"
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select


+-------------------------------------------------------------------+
|Filename: BulkRentalCalculator.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=68|
+-------------------------------------------------------------------+

--
herbwarri0r

------------------------------------------------------------------------
A poll associated with this post was created, to vote and see the
results, please visit http://forums.yourdomain.com.au/showthread.php?t=92356
------------------------------------------------------------------------
Question: What do you think?

- Wow
- Very Good
- OK
- Could be better
- Poor
------------------------------------------------------------------------

herbwarri0r Wrote:
> Hi all,
>
> I was helped out earlier on the General Discussion forum so I thought
> I'd upload my greatest excel achievment!

Right, I've managed to upload the rental clac by deleting masses of
stuff out of the workbook. 39K is a bit tight even for a zip
ExcelBanter.com.


+-------------------------------------------------------------------+
|Filename: Adjustment Calculator v2.5.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=69|
+-------------------------------------------------------------------+

--
herbwarri0r