Formulas......

[Anx]

I have this formula where if a cell has the word refurbished in it then a calculation is performed...

e.g.

=IF(H6="Refurbish",J6/100*'USER SHEET'!C19)

What i need to do is add a 'what if' the cell has the word "new" in it instead of refurbished and perform a different calculation, is it possible to add this to the existing formula please?


Also i need some help with dates....

How is it possible to work out how many months old date x is from a certain variable date y ?

e.g. I have a list of dates and need to work out how many months ago they are from another variable date.


Thanks for your help

Anx

[eliW]

On 2002-04-15 10:47, Anx wrote:
I have this formula where if a cell has the word refurbished in it then a calculation is performed...

e.g.

=IF(H6="Refurbish",J6/100*'USER SHEET'!C19)

What i need to do is add a 'what if' the cell has the word "new" in it instead of refurbished and perform a different calculation, is it possible to add this to the existing formula please?

Anx,
To the first part of your question:

=IF(H6="Refurbish",J6/100*'USER SHEET'!C19,IF(H6="new",[enter your formula],""))

Eli


[ This Message was edited by: eliW on 2002-04-15 11:18 ]

[jfuoti]

What you need is a nested if:

=IF(H6="Refurbish",J6/100*'USER SHEET'!C19, IF(cell = "New", formula, ""))

As for the date simply use the minus function: date1 - date2, this will give you the number of days between dates

[Ian Mac]

the formula:

=IF(H6="Refurbish",J6/100*'USER SHEET'!C19)

is currently incomplete, IF() being:

IF(logical_test,value_if_true,value_if_false)

so you can modify your formula to:

=IF(H6="Refurbish",J6/100*'USER SHEET'!C19,IF(H6="New",Your_Calc,"")).

the "" tells excel to put NOTHING in the cell if the second if statment's argument is false.

Look at the help files for NESTED IF().

The second question you ask is a little more tricky, it depends on what you define as a month.

does it include part months? i.e. 6months 29days = 7 months
or does it not? 6months 29days = 6 months
would you like to include .something of a month?
do you need to round up or down from a given day in a month?

[Aladin Akyurek]

On 2002-04-15 10:47, Anx wrote:
I have this formula where if a cell has the word refurbished in it then a calculation is performed...

e.g.

=IF(H6="Refurbish",J6/100*'USER SHEET'!C19)

What i need to do is add a 'what if' the cell has the word "new" in it instead of refurbished and perform a different calculation, is it possible to add this to the existing formula please?


Also i need some help with dates....

How is it possible to work out how many months old date x is from a certain variable date y ?

e.g. I have a list of dates and need to work out how many months ago they are from another variable date.


Thanks for your help [img]/board/images/smiles/icon_smile.gif[/img]

Anx

Gee Anx. Why start another thread? Now I've lost the connection, because I cannot do a Topic Review as easily.

You can change

=IF(H6="Refurbish",J6/100*'USER SHEET'!C19)

to:

=IF(H6="Refurbish",J6/100*'USER SHEET'!C19,IF(H6="New",another-calc-here,"?"))

You "need" to fill in for "?" when H6 is not "Refurbish" or "New".

The date question:

=MONTH(B1)-MONTH(A1)

where A1 and B1 houses properly formatted dates and A1 is earlier than B1.

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-04-15 11:06 ]

[Ian Mac]

On 2002-04-15 11:05, Aladin Akyurek wrote:

Gee Anx. Why start another thread? Now I've lost the connection, because I cannot do a Topic Review as easily.

You can change

=IF(H6="Refurbish",J6/100*'USER SHEET'!C19)

to:

=IF(H6="Refurbish",J6/100*'USER SHEET'!C19,IF(H6="New",another-calc-here,"?"))

You "need" to fill in for "?" when H6 is not "Refurbish" or "New".

The date question:

=MONTH(B1)-MONTH(A1)

where A1 and B1 houses properly formatted dates and A1 is earlier than B1.

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-04-15 11:06 ]

I had thought of =MONTH(B1)-MONTH(A1)

but I thought I'd ask the questions, the only thing that it won't take into account is going from 1 year to the next.

however:

=((YEAR(B1)-YEAR(A1))*12)+(MONTH(B1)-MONTH(A1)) _Format as General

should accommodate this.

thoughts?

_________________
Share the wealth!!
Ian Mac

[ This Message was edited by: Ian Mac on 2002-04-15 11:15 ]

[Anx]

Excellent thanks, i now have..

=IF(H14="Refurbish",J14/100*'USER SHEET'!C19,IF(H14="New",J14*1,""))

the J14*1 is just to copy the data across if cell H14=new, I'm not sure this is the 'right' way to do this but it works.

Just one slight problem...when I auto-fill the line below (around 600 odd) the 'USER SHEET'C19 changes as well, ...going to 20 on the next line and 21 on the next etc etc.

Is there a way to stop this please? I only need them to reference C19 whilst for all the other cell references auto copying is fine..

Thanks

(sorry about the 2nd thread)

Anx


[ This Message was edited by: Anx on 2002-04-15 11:47 ]

[Aladin Akyurek]

Just freeze that changing bit: all it needs is 2 $$ :

'USER SHEET'!$C$9

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-04-15 11:59 ]

[Anx]

Hi, this doesnt seem to work, it gives me incorrect calculations now.

Anx

[Aladin Akyurek]

On 2002-04-15 12:54, Anx wrote:
Hi, this doesnt seem to work, it gives me incorrect calculations now.

Anx

How do you mean? You wanted to keep this unchanged while copying down the formula of which it is a part. Maybe the whole formula itself is not doing what you expect it to do. Maybe you should post the complete formula that you're dragging down.

Aladin