Need a fresh pair of eyes

[ibvalentine]

I just created a worksheet that is more complicated than it looks and I want
to make it available for anyone who would do my the favor of looking it over.

The difficulty of the worksheet is that you have to considered all possible
parameters, some of which may not be obvious. So, if you can see any possible
parameters that the formulas may not cover, please let me know. Also, I may
have overlooked some errors.

The objective of the worksheet is to calculate storage fees for April. The
difficult part is that items come in at different times and have different
levels of charges. There is a table in columns L and M that breaks the
charges down.

You can down load the Excel file by going to this page:
http://www.icellini.com/excel.html.

Thanks to anyone that takes a look!

 

[JBeaucaire]

In columns G,H, I you *appear* to "tier" the pricing for some days. But you
don't seem to do that in column F. So you need to clarify for me HOW you
apply your pricing tier.

Do you...
1) count the number of days and find the rate on the chart, then charge the
single rate to ALL the days in April
2) Charge .25 a day for the first 3 days, 1.01 per day for the next 30, 1.25
per day for the next 30, and finally 1.56 per day for all days after 60.
3) Charge to top TWO rates applicable to the row (this appears to be what
you may be doing.)

You can respond to me directly if you wish at:
jerry AT devstudios DOT com

(turn that into a normal email address)

 

[JBeaucaire]

Assuming all your other formulas are correct, you can simplify your formula
in J7 to this, then copy down:

=IF(E7="","",SUMPRODUCT($F$6:$I$6*F7:I7))

 

[JBeaucaire]

In general, you are working far harder than you have to. This often occurs
when people try to create "monthly" sheets to create data in instead of
creating monthly "reports" off of a flat data sheet. Very common.
Unnecessary headaches.

For instance...one simple stress-relieving approach is to simply have you
chart WITHOUT the "Days Before April" and the "Days In April"
columns...instead, just have one column called "Days so far".

Then let the 4 charge columns do MUCH simpler work on the one number.
B7: Start date

C7: End date - starts off with the formula
=TODAY()
....replace that formula with actual closing date when item picked up, until
then the sheet just keeps accruing days.

D7: Days so far
=C7-B7

E7: .25
=MIN(3,D7)

F7: 1.01
=IF($D7-SUM($E7:E7)=0,0,MIN(27,$D7-SUM($E7:E7)))

G7: 1.25
=IF($D7-SUM($E7:F7)=0,0,MIN(30,$D7-SUM($E7:F7)))

H7: 1.56
=IF($D7="",0,$D7-SUM($E7:$G7))

I7: First month of charges
=IF($D7="","",SUMPRODUCT($E$6:$H$6*$E7:$H7))

Now, copy those formulas down.

Then in J7 put this for Next month's charges:
=IF($D7="","",SUMPRODUCT($E$6:$H$6*$E7:$H7))-SUM($I7:I7)

...and copy down and over for the whole year.

How this helps. Now, when you issue a bill you will see a column of
"current values" I7. replace the formulas in those cells with what you
"billed" the customer for that month. Enter a real number.

Now, if the C column still has the =TODAY() formula in it, then the J column
will start to accrue new charges on all its own the next day. At the end of
second month, you will have a whole new set of charges...basically the
"difference" between what has already been billed and what is due as a total.

Anytime you change a C column date to a final date, that row will stop
accruing charges and that month's billing will be the last.

I have this all mocked up if you email me.

 

[ibvalentine]

Jerry,

Thanks for looking at the worksheet. I don't know if you got a chance to
review the formulas; it's very time-consuming.

Regarding your question in your first post, the fee schedule is in columns L
and M. The breakdown is as follows:

0-3 days, $0.25
4-30 days, $1.01
31-60 days, $1.25
60+ days, $1.56

Your suggestion to use the sumproduct function on that last formula was a
good one and I updated the worksheet using that function. The formulas I am
most concerned with are the ones in F7, G7, H7, and I7. I don't know if you
had a chance to review those; this particular worksheet is pretty time
consuming. At any rate, I really appreciate your help!

Thanks,

John

 

[ibvalentine]

Hi Jerry,

There is one thing that I obviously did not make clear. These storage fees
are paid on a monthly basis and the worksheet needs to determine what the
fees are for April only. Therefore, your suggestion will not work because the
fees before April have already been paid.

Like I said, this is more complicated than it looks. For example, the fifth
record shows an item coming in on 3/13/09 and finished on 4/20/09. That's 39
days, but 19 of those days have already been billed in March and the
worksheet needs to calculate the billing only for the 20 days in April. Well,
11 of those days will be billed at $1.01 each and 9 of those days will be
billed at $1.25 each for a total of $22.36.

By the way, I didn't come up with this plan. This is for a client of mine
that I am trying to do a favor for. I think the formulas I came up with are
not going to get any simpler, but I would love to see someone prove me wrong.
And like I mentioned before, I may have made errors, but I can't see any.

Thanks again for your time. If you are intrigued with this Excel problem, I
would welcome more input from you but I don't want to impose on your time.

John

 

[ibvalentine]

I should try to further clarify the problem. The reason for the Days before
April column is to determine at what level the fee schedule will be applied
to the April storage fees. As I mentioned, the fifth record shows an item
coming in on 3/13/09 and finished on 4/20/09. That's 39 days, but 19 of those
days have already been billed in March and the worksheet needs to calculate
the billing only for the 20 days in April.

Why are 11 of those days being billed at $1.01 each and 9 of those days will
be billed at $1.25 each? Because of the 19 previously days in storage before
April, the first 11 days in April fall on the second level of the fee
schedule (4-30 days @ $1.01) and the last 9 days fall on the third level of
the fee schedule (31-60 days @ $1.25).

 

[John]

Hi
I believe there is a mistake in your formula.
Change the starting date on the first line to feb.20
Sample from your file :2/20/2009 4/1/2009 40 1 0 0 11 0
$13.75
It's giving 11 days in April but if you change it to March, its giving 1 which I
think it should be right. I'm not sure why it's not working in February but
working in January & March.
Regards
John

 

[ibvalentine]

"John" wrote:
John,

Thanks for finding that error. the first nested if statement had D7>60
instead of D7>30 for the logical test.

 

[John]

Hi ibvalentine
You're welcome but still not right,changing D7>60 to D7>30 will change tthe
number to 1 but it is moving to the $1.56 section. the total number of days is
41, it should stay in the 1.25$ section.I think I would use a different approach
but sorry I don't have the time at this moment.
HTH
John

 

[Herbert Seidenberg]

Excel 2007
Total make-over.
Defined Names, Tables, Struct. Refs.
http://www.mediafire.com/file/my0d2ztnimq/04_29_09a.xlsx

 

[John]

Hi Ibvalentine
I notice that you changed your sample file.
You're not calculating the same way, but still not working every time.
I took your old form and simplified the formulas, kept your format, are you
still interested.

Regards
John