Help with calculations in Excel

[Joel]

Welcome one and all to the most annoying Excel problem I have come across.

My problem is as follows, I have set up an Excel spreadsheet to help me pay
my credit card plus keep an eye on how long it will take to pay the balance
off.

Here is a list of my cells to help you understand what I have done.

R4 is my Credit Limit £3500

R6 is what I have spent on my credit card £3278.95

R8 is what I have available to spend the calculation =sum(R4-R6) £221.05

R10 is my APR 17.9%

R12 is my monthly interest rate of 1.385

R14my minimum payment is worked out with the follow calculation
=ROUND(R6*0.02,2) £65.58

C20 I put the date of payment

J20 I have a section for where the money came from

O20 is the amount I pay to the card £65.58


Now above are all my figures, below what I am trying to do with those figures

I am trying to workout my minimum payment so that once I have made a payment
it will automatically tell me what next months minimum repayment will be

I have a little table to help me with my calculations

AE19 I have a carried forward balance £3278.95

U20 which is =AE19

W20 is the total amount owed minus my payment =sum(U20-O20) £3213.37

Y20 is the amount of monthly interest which is Full amount – amount paid
=SUM(U20*R12) (45.41)

AA20 Amount carried forward which is Full amount minus amount paid + Amount
of monthly interest =SUM(W20+Y20) £3258.78

What I am trying to achieve without any real success is to get it to work
out all the calculations for me so all I have to do is put in the amount I
wish to pay into O column and then it should automatically calculate my
monthly interest and work out my new balances for me and then carry to the
line below so that the calculation can then be worked out

I have worked out of to do it but when I dray the calculations down it
calculates even though I not paid anything yet which puts my minimum
repayments out

Sorry it is so long winded. This issue is driving insane please help I have
been on it for weeks thanks

Joel

 

[Pete_UK]

Maybe you just need something like:

=IF(O20="","",your_existing_formula)

so that if you have not put anything in O20 you will just get a blank
cell returned, but in other cases you will get the calculation.

Hope this helps.

Pete

 

[Joel]

Hi Pete thank you for your reply I am not sure where to put that formula can
you please advise

Regards Joel

 

[Pete_UK]

Hi Joel,

you did say:

"...I have worked out of to do it but when I dray the calculations
down it
calculates even though I not paid anything yet which puts my minimum
repayments out..."

so i presumed you had a number of calculations which you drag down.
You can amend each of those on one row (the first row with the
formulae in) along the lines I suggested, and then when you drag them
down you will get a blank cell if the entry in column O is blank.

Hope this helps.

Pete

 

[Joel]

Hi Pete thanks for your understanding especially as I appear a bit thick.

Just to clarify I have the following


I have 1 column called Amount in this column I put down how much I pay my
credit card This starts at O20 and carries through to O41 it this column that
should trigger the calculations

Then I have a calculations table with 3 columns. First column is called Full
amount minus amount paid

I have the Full amount I owe on my credit card hidden in cell Z19 wich is
£3278.95 I take the cell Z19 and minus the amount if have choosen to pay
which is in cell O20

My calculation is =IF(O20="","",(Z19-O20)) which gives me the answer
£3213.37 which is correct.This is in Cell V20

Second Column Called Amount of Monthly Interest Which is the the amount I
owe multiply the amount of interest I.e 1.385

In this column I calculate my Monthly interest. My monthly interest remains
the same every month so I just multiply

My Calculation is as follows =IF(O20="","",(V20*R12)) which gives me the
answer £44.51 wich is correct. This is in Cell X20

Third Column is called Carried Forward Balance

In this column I just add the the Full Amount minus the amount I paid which
£3213.37 and then add the interest payment of £44.51.

The calculation I use for this is as follows =IF(O20="","",(V20+X20)) wich
give me my new balance of £3257.88 which is correct. this is in Cell Z20

All the above a calulations are correct rather than do them all indivual I
though I would just drag them but that does not work as it calculates
regardless as to wether I have paid an amount or not

I only want it to calculate when I enter an amount in The O column and for
it only calculate that row.

I hope this helps to explain my problem if need be I can send a copy of the
sheet to show what I mean.

Regards Joel

 

[Pete_UK]

Hi Joel,

If you copy those formulae down, then they should become:

=IF(O21="","",(Z20-O21))
=IF(O22="","",(Z21-O22))
=IF(O23="","",(Z22-O23))

in column V, for example, and if O21 is empty then the first one will
return a blank, if O22 is empty then the second one will be blank, and
so on.

Is this not what you want?

I don't see how they are trying to calculate if the cells in column O
are empty, unless they are not truly empty (eg if they contain one or
more spaces and just look empty).

Pete

 

[Joel]

Hi Peter

I am to understand the the formule you have shown me goes in the V column
and the V column only is this right? and the O column is not empty because it
has a Zero it because it is a currency number.

Regards Joel

 

[Pete_UK]

Hi Joel,

I gave you an example for the formulae in column V only, but the same
would apply for the formulae in columns X and Z when you copy those
down.

If you have 0 in the cells in column O this is not the same as them
being blank, so the general formula I have given to you will not work
- I had assumed that the cells were empty until you put something in
them, i.e. each month you decided how much to pay and would put that
amount in the cell in column O. So, you need to delete the contents of
the cells in column O for the months you have not got to yet.

Hope this helps.

Pete

 

[Joel]

Dear Pete

You are any absolute star, that works a treat thank you very much, can I
just ask one final questionin my Balance Carried Forward Column how can I ge
te last one when I fit n an place it in my Cell that says how much I owe

So I want R6 to have what ever the last filled in Cell in the Carried
forward column is

Regards and thanks a million Joel

 

[Pete_UK]

Glad it's working so far.

I think your Balance Carried Forward Column is column Z, and your data
starts in row 20 - assume it may go up to row 50, so put this formula
in R6:

=LOOKUP(10^10,Z20:Z50)

This will give you the last filled cell in the range Z20:Z50.

Hope this helps.

Pete

 

[Joel]

Cheers Pete

Thanks for all your help

Joel

 

[Pete_UK]

You're welcome, Joel - glad to help.

Pete

Cheers Pete

Thanks for all your help

Joel
--
N/A







- Show quoted text -

 

[ma_bird]

Could you please send me a "blank" template. Would really appreciate this?
(e-mail address removed)
Thanks!

 

[Jeff]

Can someone help me with a New Question? I am working on salary/bonus
figures. If the net profit in cell A50 is >0 then the salary figure in cell
A40 should be A40*5% of A50 - how do I create a formula for this? Anybody's
help would be much appreciated!
Jeff

 

[Fred Smith]

You can't. A cell can have values or formulas, not both. You can't have a
formula in A40 which refers to A40. That's a circular reference, and will
never be resolved. In some other cell, enter the formula:
=if(a50>0,a40*a50*.05,"what you want when a50 is not greater than 0")

In the future, when you have a new topic, start a new thread. Don't hijack
some other thread.

Regards,
Fred