Formula Help

[Jonathan Marshall]

HI

I am trying to work out a formula for the following scenario -:

I have a date cell in a spreadsheet which is populated with the date I receive a payment from a client.

I want to put a formula into a second cell which pulls an amount from third cell cell if the date has not been input. (the date cell is blank)

However if a date is input into the date cell i want the sum in the second cell to show as zero/blank.

Can anyone help?

Thanks

Jon

 

[kidwispa]

HI

I am trying to work out a formula for the following scenario -:

I have a date cell in a spreadsheet which is populated with the date I receive a payment from a client.

I want to put a formula into a second cell which pulls an amount from third cell cell if the date has not been input. (the date cell is blank)

However if a date is input into the date cell i want the sum in the second cell to show as zero/blank.

Can anyone help?

Thanks

Jon

Assuming Date is in cell A1, and "third cell" is C1, put this formula in B1

=IF(ISBLANK(A1),C1,"")

Change the cells refs to what you want and if you would prefer a zero instead of blank then change "" to 0

 

[Jonathan Marshall]

Many thanks for the help but at the moment it appears to be working the opposite way round

ie. it puts in the £amount if the date is in the 2nd cell and takes it out if it is not.

Any ideas?

Jon

 

[kidwispa]

Many thanks for the help but at the moment it appears to be working the opposite way round

ie. it puts in the £amount if the date is in the 2nd cell and takes it out if it is not.

Any ideas?

Jon

My bad.. the formula should read

=IF(ISBLANK(A1),"",C1)

 

[Jonathan Marshall]

hi

it is still the wrong way round.

it shows 0 in cell B when there is no date in cell A.

and shows the amonut in cell C when a date is put in.

It needs to show an amount in cell B when there is no date in cell A and no amount in cell B when a date is in cell A

 

[kidwispa]

hi

it is still the wrong way round.

it shows 0 in cell B when there is no date in cell A.

and shows the amonut in cell C when a date is put in.

It needs to show an amount in cell B when there is no date in cell A and no amount in cell B when a date is in cell A

Hi Johnathan,

The (second) formula I have given you is as follows:

If cell A1 (where you have the date) is blank, then leave B1 blank, otherwise show value from C1. I have tested this and it works fine for me...

Alternatively try this...

=IF(A1="","",C1)

EDIT - One thing I forgot to ask - is there ever anything else in cell A1 other than the date?

 

[Jonathan Marshall]

I see the problem

needs to be -;

If cell A1 (where you have the date) is blank, then value/amount from C1 required to be shown in B1

If cell A1 (where you have the date) is ipopulated, then B1 needs to = nil

Jon

 

[kidwispa]

I see the problem

needs to be -;

If cell A1 (where you have the date) is blank, then value/amount from C1 required to be shown in B1

If cell A1 (where you have the date) is ipopulated, then B1 needs to = nil

Jon

Ok - then either of these will work fine:

=IF(ISBLANK(A1),C1,0)

=IF(A1="",C1,0)

 

[Jonathan Marshall]

That works - superb !

many thanks for your help !!!!!

Jonathan

 

[kidwispa]

That works - superb !

many thanks for your help !!!!!

Jonathan

No worries