+ Reply to Thread
Results 1 to 11 of 11

Altering Parameters for IF Formula

  1. 03-31-2014, 01:52 AM #1
    quibilty
    quibilty is offline
    Forum Contributor
    Join Date
    08-25-2013
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2013
    Posts
    205

    Altering Parameters for IF Formula

    I need to modify the following formula, =IF(G5=-105,-105,-G5-IF(ABS(G5)>251,40,IF(AND(ABS(G5)>221,ABS(G5)<251),30,IF(AND(ABS(G5)>180,ABS(G5)<221),20,IF(AND(ABS(G5)>=100,ABS(G5)<181),10,105))))) so that I get the following IF-THEN results:

    IF G= -105 then value of I = -105

    IF G = -110 to -160 then value of I = (100 to 150) -10
    IF G = -165 to -200 then value of I = (150 to 185) -15
    IF G = -205 to -245 then value of I = (185 to 225) -20
    IF G = -250 to -275 then value of I = (225 to 250) -25
    IF G = -280 to -315 then value of I = (250 to 285) -30
    IF G = -320 to -350 then value of I = (285 to 315) -35
    IF G = -355 or lower then value of I = (315 to ....) -40

    And also...

    IF G = 100 to 150 then value of I = (-110 to -160)
    IF G = 150 to 185 then value of I = (-165 to -200)
    IF G = 185 to 225 then value of I = (-205 to -245)
    IF G = 225 to 250 then value of I = (-250 to 275)
    IF G = 250 to 285 then value of I = (-280 to -315)
    IF G = 285 to 315 then value of I = (-320 to -350)
    IF G = 315 or higher then value of I = (-355 to ....)

    Attached is a sample that should help. Thanks.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-31-2014, 01:54 AM #2
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Altering Parameters for IF Formula

    Please describe in words


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved

    If the suggestion helps you, then Click *below to Add Reputation
    Reply With QuoteRegister To Reply
  3. 03-31-2014, 02:06 AM #3
    quibilty
    quibilty is offline
    Forum Contributor
    Join Date
    08-25-2013
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2013
    Posts
    205

    Re: Altering Parameters for IF Formula

    Just use the example file. G3 = -160 then I3 = 150, which is what I want. G4 = -165 then I4 = 155, but I want I4 to = 150 (as referenced in my explanation). G8 = -255 and I want I6 to = 230, not 215.

    Does this help?
    Reply With QuoteRegister To Reply
  4. 03-31-2014, 02:26 AM #4
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Altering Parameters for IF Formula

    Please describe which is the expected result cell/column?
    Reply With QuoteRegister To Reply
  5. 03-31-2014, 02:55 AM #5
    quibilty
    quibilty is offline
    Forum Contributor
    Join Date
    08-25-2013
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2013
    Posts
    205

    Re: Altering Parameters for IF Formula

    ...f......g........h.....i.......j
    -105 -105 -105 -105 -105
    -110 -110 -110 100 100
    -115 -115 -115 105 105
    -120 -120 -120 110 110
    -125 -125 -125 115 115
    -130 -130 -130 120 120
    -135 -135 -135 125 125
    -140 -140 -140 130 130
    -145 -145 -145 135 135
    -150 -150 -150 140 140
    -155 -155 -155 145 145
    -160 -160 -160 150 150
    -165 -165 -165 150 150
    -170 -170 -170 155 155
    -175 -175 -175 160 160
    -180 -180 -180 165 165
    -185 -185 -185 170 170
    -190 -190 -190 175 175
    -195 -195 -195 180 180
    -200 -200 -200 185 185
    -205 -205 -205 185 185
    -210 -210 -210 190 190
    -215 -215 -215 195 195
    -220 -220 -220 200 200
    -225 -225 -225 205 205
    -230 -230 -230 210 210
    -235 -235 -235 215 215
    -240 -240 -240 220 220
    -245 -245 -245 225 225
    -250 -250 -250 225 225
    -255 -255 -255 230 230
    -260 -260 -260 235 235
    -265 -265 -265 240 240
    -270 -270 -270 245 245
    -275 -275 -275 250 250
    -280 -280 -280 250 250
    -285 -285 -285 255 255
    -290 -290 -290 260 260
    -295 -295 -295 265 265
    -300 -300 -300 270 270
    -305 -305 -305 275 275
    -310 -310 -310 280 280
    -315 -315 -315 285 285
    -320 -320 -320 285 285
    -325 -325 -325 290 290
    -330 -330 -330 295 295
    -335 -335 -335 300 300
    -340 -340 -340 305 305
    -345 -345 -345 310 310
    -350 -350 -350 315 315
    -355 -355 -355 315 315
    -360 -360 -360 320 320
    -365 -365 -365 325 325
    Reply With QuoteRegister To Reply
  6. 03-31-2014, 03:04 AM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: Altering Parameters for IF Formula

    Posting a bunch of numbers like that still does not explain what you want. Remember, although what you want is peferctly clear to you, we have no idea what you are doing.

    Please walk us through exactly what you want
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  7. 03-31-2014, 03:24 AM #7
    quibilty
    quibilty is offline
    Forum Contributor
    Join Date
    08-25-2013
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2013
    Posts
    205

    Re: Altering Parameters for IF Formula

    Sorry for the confusion. I solved it myself. Thanks.
    Reply With QuoteRegister To Reply
  8. 03-31-2014, 03:24 AM #8
    quibilty
    quibilty is offline
    Forum Contributor
    Join Date
    08-25-2013
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2013
    Posts
    205

    Re: Altering Parameters for IF Formula

    =if(g4=-105,-105,-g4-if(abs(g4)>354,40,if(and(abs(g4)>319,abs(g4)<354),35,if(and(abs(g4)>279,abs(g4)<319),30,if(and(abs(g4)>249,abs(g4)<279),25,if(and(abs(g4)>204,abs(g4)<249),20,if(and(abs(g4)>161,abs(g4)<204),15,if(and(abs(g4)>=100,abs(g4)<161),10,105))))))))
    Reply With QuoteRegister To Reply
  9. 03-31-2014, 03:28 AM #9
    Izandol
    Izandol is offline
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Altering Parameters for IF Formula

    Perhaps a lookup table?
    Attached Files Attached Files
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules
    Reply With QuoteRegister To Reply
  10. 03-31-2014, 03:39 AM #10
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Altering Parameters for IF Formula

    aside from using a table as suggested which i think is more efficient (editing the values directly)
    can you try something like
    =-G4-LOOKUP(ABS(G4),{0,100,162,205,250,280,320,355},{105,10,15,20,25,30,35,40})
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir
    Reply With QuoteRegister To Reply
  11. 03-31-2014, 03:48 AM #11
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Altering Parameters for IF Formula

    Me too spent some time for this, so I am posting the content along with your's


    N
    O
    P
    3
    >
    <
    Slab
    4
    100
    161
    10
    5
    161
    204
    15
    6
    204
    249
    20
    7
    249
    279
    25
    8
    279
    319
    30
    9
    319
    354
    35



    K
    3
    =SUMIFS($P$4:$P$9,$N$4:$N$9,"<"&ABS(G3),$O$4:$O$9,">"&ABS(G3))



    L
    3
    =IF(G3=0,0,IF(G3=-105,-105,IF(K3,ABS(G3)-K3,105)))


    Drag both formula's down...

    Refer the attached file for details
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. altering cell references within a formula
    By jasonineverett in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-27-2012, 09:23 PM
  2. Altering formula for errors
    By pctuk in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-05-2010, 08:16 AM
  3. Altering VLookup Formula
    By addie in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-15-2006, 09:10 PM
  4. [SOLVED] Values won't display after altering a formula.
    By Mike O in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-13-2005, 08:06 PM
  5. Formula keeps altering by itself!
    By Julie P. in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-02-2005, 07:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1