H
herbwarri0r
-----------------------------------------------------------------------
A poll associated with this post was created, to vote and see th
results, please visit http://forums.yourdomain.com.au/showthread.php?t=9235
-----------------------------------------------------------------------
Question: What do you think
- Wo
- Very Goo
- O
- Could be bette
- Poo
-----------------------------------------------------------------------
Hi all,
I was helped out earlier on the General Discussion forum so I though
I'd upload my greatest excel achievment!
At work we had a simple calculator to work out the cost of a produc
over a date range and calculate it acurately on a per day basis.
wanted to improve upon this so that only a from and to date had to b
entered and I have done, plus it calculates many products at once.
There are also macros now to paste lines into another workbook whic
can work out the next empty row. Using a counter wasn't an option a
some lines would be added manually.
There are obvious limitations with this due to IF. Also if the price o
a product chages during the period you are calculating so have to us
the Original sheet. If anyone can make my meager contribution mor
ellagant then please feel free to assimilate and regurgetate! I'v
really go to work on the presentation of this as well.
Most of the formulas are hidden below and to the right of the visabl
sheets, but there are other hidden sheets you may want to look at. Th
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 th
bulk calculator. Oh well the formula I used to get the next free ro
was:
Application.Goto Reference:="R7C1"
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Selec
+-------------------------------------------------------------------
|Filename: BulkRentalCalculator.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=68
+-------------------------------------------------------------------
A poll associated with this post was created, to vote and see th
results, please visit http://forums.yourdomain.com.au/showthread.php?t=9235
-----------------------------------------------------------------------
Question: What do you think
- Wo
- Very Goo
- O
- Could be bette
- Poo
-----------------------------------------------------------------------
Hi all,
I was helped out earlier on the General Discussion forum so I though
I'd upload my greatest excel achievment!
At work we had a simple calculator to work out the cost of a produc
over a date range and calculate it acurately on a per day basis.
wanted to improve upon this so that only a from and to date had to b
entered and I have done, plus it calculates many products at once.
There are also macros now to paste lines into another workbook whic
can work out the next empty row. Using a counter wasn't an option a
some lines would be added manually.
There are obvious limitations with this due to IF. Also if the price o
a product chages during the period you are calculating so have to us
the Original sheet. If anyone can make my meager contribution mor
ellagant then please feel free to assimilate and regurgetate! I'v
really go to work on the presentation of this as well.
Most of the formulas are hidden below and to the right of the visabl
sheets, but there are other hidden sheets you may want to look at. Th
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 th
bulk calculator. Oh well the formula I used to get the next free ro
was:
Application.Goto Reference:="R7C1"
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Selec
+-------------------------------------------------------------------
|Filename: BulkRentalCalculator.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=68
+-------------------------------------------------------------------