Formulas......
Formulas......
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Formulas......

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?
    "Have a good time......all the time"
    Ian Mac

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,823
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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 ]

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,823
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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 ]

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Anx

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,823
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    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


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com