Formulas......

Anx

New Member
Joined
Mar 31, 2002
Messages
22
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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 /board/images/smiles/icon_smile.gif

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
 
Upvote 0
On 2002-04-15 11:05, Aladin Akyurek wrote:
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 :)

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

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
 
Upvote 0
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
 
Upvote 0
Just freeze that changing bit: all it needs is 2 $$ :biggrin::

'USER SHEET'!$C$9

Aladin
This message was edited by Aladin Akyurek on 2002-04-15 11:59
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top