Calc. in Excel 2003 Help Needed-Amt Due for each Day and Pay type

[Snookless7]

Hello and Thank you in advance.Your help is much appreciated.

i am trying to calculate amounts based off of date payment is Due, the amount that is due and if the payment will come via Visa, MC...etc.

Customers are allowed to come to my store and decide if they would like to split the total cost of their merchandise into 4 payments

So in one column (A) I have the payment type (Visa, Master card..etc)
The next column(B) I have the amount of each payment
The next 4 columns(C,D,E and F) I have the date each payment is due if they decide to split it up.(If the payment is split into 2 payments I would only use C and D to record the due dates)

What I would like to have now is a seperate worksheet showing me (for example)..on Febuary 4th:
I have a total of $396.65 due via Master card
I have a total od $78.00 due via Visa

I would like to have a record for each day of the month.

Any suggestions?

Your help is much appreciated.

Thank you,

Todd

 

[SkipVought]

Hi,

Yes. Resturcture your table to NORMALIZE the data. This means...
[tt]
Customer PmtTyp PmtAmt PmtDte
[/tt]
This assumes that a customer can only have ONE payment plan active at one time.

With this kind of structure, you could get a report of receivables, for any day, week, month that you would like in mere SECONDS, using a PivotTable.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

MUCH easier to have one row for each payment rather than a column for each. Is this totally out of the question?
If you will not entertain collecting the data in that way then I would use a macro to re-configure the data into that format.

Alternatively I would look at a table of dates and and types using sumproduct formulae to get your results.
or maybe,
Column for each date, beside your data with a formula testing if any of the columns CDEF matched the date or that column (in row1. Something like:
=if(OR($C2=h$1,$D2=h$1,$E2-h$1,$F1=h$1),$B2,"")
Then sum the columns.
(Second thoughts the column total formulae would be in row1 and the dates in row2)

Gavin

 

[Snookless7]

Well, the pivot table seems a little easier (for me anyways) the only problem is the pivit table is couting the payments, for example I should have a monetary value but instead it is just counting how many payments there are.

Sorry, I am bearly a technical user.


Thanks.

 

[SkipVought]

You can COUNT, SUM, AVERAGE, MAX, MIN.

Choose in the Field Setting.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}