Excel formula required

  Bruz 08:34 15 Jan 2008
Locked

I am trying to get excel to automatically add 25 to a cell on a given date.
Using the DAY NOW function I can generate the 25 on any day of the year but when I try to add it to the number already in the cell I get the Circular reference error.
Any one got any ideas?
Cheers Bruz

  johnnyrocker 09:01 15 Jan 2008

what about using 'if' formula?


johnny.

  Bruz 09:19 15 Jan 2008

Thanks johnyrocker for the input.
I am using a IF formula to get the date and the amount. IF((DAY(NOW())=(23),25,"") this gives me 25 on the 23rd day and a blank cell on any other day, what I now need is a formula to add the 25 to a number in another cell. I have tried to use the true part of the function to do this, it either just displays the formula or says false.
Can you suggest a formula to put in the function to make it work. I would be very grateful if you could.
Bruz

  johnnyrocker 09:21 15 Jan 2008

sounds like one for vog, i am sure he will be along shortly,


johnny

  Bruz 09:46 15 Jan 2008

Thanks johnyrocker for the input.
I'll wait for vog.
Cheers Bruz

  bstb3 10:26 15 Jan 2008

Try this one

=IF(IF(DAY(NOW())=(15),25,"")="","",B1+IF(DAY(NOW())=(15),25,""))

It assumes that the cell you want to add the 25 to is B1, obviously you can change this to whatever you want it to be. If the date is not the correct one, it will display a blank (the middle ,"", section). Again this can be changed to suit.

You will have to reference a different cell however, this cannot all be applied to one cell. It may be possible to do something via VBA to do this, but thats beyond me. Hope this helps anyway.

  Bruz 11:51 15 Jan 2008

Hi bstb3
That formula does the same as all my attempts did, it adds 25 to the formula cell.
I copied the formula into cell A49 and changed B1 to B51. In cell B51 I entered 25 and when I entered 15 as the date cell B49 displayed 50.
I then entered +A49 into A52 and that displayed 50 but I changed the date from 15 to 16 A52 became blank.
Cheers Bruz

  Picklefactory 13:19 15 Jan 2008

Not sure I'm understanding.
Your first thread seemed to imply (To me anyway), that you want to change the value in one cell, depending on a given date, your later post seemed to suggest you wanted to add the value from a different cell to the value in the first cell based on the date. Maybe I'm being a bit thick, but not quite grasping what you're after, could you maybe give some example data eg cell A1=25, cell A2=IF((DAY(NOW())=(23),25,"") and cell A3=12 or some such, that would help me at least.

  bstb3 13:20 15 Jan 2008

Using your new cell references, the following formula should display either 25 or 50 in both A49 and A52. 25 when the date is not 15, 50 when it is.

=IF(IF(DAY(NOW())=(15),25,"")="",B51,B51+IF(DAY(NOW())=(15),25,""))

I think that is what you are trying to get to?

  Picklefactory 13:28 15 Jan 2008

I'm not sure this is what you want, but if you want to add 25 to a given cell on a given date you will need to use seperate cells. EG
cell A1=IF(DAY(NOW())=15,25+B1,"") where cell B1=10 the result in A1 would be 35, is that anything like what you want?

  Picklefactory 13:31 15 Jan 2008

Or, if I'm understanding at all, A1=IF(DAY(NOW())=15,25+B1,25) and if B1=10 then A1 would give 35 on the 15th day and 25 any other day. Still not sure I'm looking at this right.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Microsoft Paint set to die after 32 years

Mac power user tips and hidden tricks

Comment désactiver la saisie intuitive et paramétrer votre clavier ?