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(a500,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

"Jeff" wrote in message
...
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

"ma_bird" wrote:

Could you please send me a "blank" template. Would really appreciate
this?

Thanks!

"Pete_UK" wrote:

You're welcome, Joel - glad to help.

Pete

On Dec 7, 12:46 pm, Joel wrote:
Cheers Pete

Thanks for all your help

Joel
--
N/A



"Pete_UK" wrote:
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

On Dec 7, 11:06 am, Joel wrote:
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
--
N/A

"Pete_UK" wrote:
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

On Dec 7, 10:22 am, Joel
wrote:
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
--
N/A

"Pete_UK" wrote:
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

On Dec 6, 6:00 pm, Joel
wrote:
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

--
N/A

"Pete_UK" wrote:
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

On Dec 6, 11:45 am, Joel
wrote:
Hi Pete thank you for your reply I am not sure where
to put that formula can
you please advise

Regards Joel
--
N/A

"Pete_UK" wrote:
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

On Dec 6, 10:22 am, Joel
wrote:
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

--
N/A- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -