Comments on: How to use IF function in Excel: examples for text, numbers, dates, blanks

IF is one of the most popular and useful functions in Excel. Generally, you use an IF statement to test a condition and to return one value if the condition is met, and another value if the condition is not met. Continue reading →

Comments page 61. Total comments: 4830

  1. Hi

    Can anyone help me on this? I am creating a formula for time and date. If the time exceed at 5:00 PM. The date and time will appear the next day and the time will be 9:00 AM.

    Example

    8/1/2017 5:00 PM result 8/1/2017 5:00
    8/1/2017 5:02 PM result 8/2/2017 9:02

    What is formula for this?

    1. Hi, your explanation and example don't quiet marry up, so I will give you 2 solutions.
      Info:
      9AM is 09:00 ( 9/24)
      5PM is 17:00 (17/24)
      From 5PM to 9AM is +1 day -8 hours or +16 hours (16/24) (2/3)
      #1 After 5PM show Tomorrow 9AM
      IF( ( A1-INT(A1)) > (17/24), INT(A1) +1 +(9/24), A1)
      #2 After 17:xx show Tomorrow 09:xx
      IF( ( A1-INT(A1)) > (17/24), A1 +(2/3), A1)
      # This can get really complex if you want ( 09:00-17:00 M-F, NO Change, Otherwise set time to next work day)

  2. Helloo, anyone would like to help me,
    how use "if function. in conditional.
    this is the situation.
    if the employee paid 100 per month and and if he employed for less that 5 years,this would be his computation for separation fee, 100/2=50/yr, otherwise if he emplyod for more than 5 yrs this would be the computation., 100/ yr.

    what is the best formula for this one.

  3. Hello!

    I have a formula I wanted to write but I am not to sure what to use.

    So here is the idea:

    On sheet 1 I have A1 with a value and in B1 I want a formula that finds the same data in A1 on Sheet 2 A:A and return the value that is in the matching A:A cell with the value that is in the next cell over (B).

    I dont know if that makes sense.

  4. =IF(AND(C9="abc", E9=TRUE), C3, IF(AND(C9="abc", E9=FALSE), D3,""))

  5. Could you please help?
    I need a warning when transposing numbers in different columns using IF function. Trying to check bank balance in (A1) with other itemised columns, only one value per row.have tried =IF((B1,C1,D1,E1,)=A1,"OK","NG") Have received #value results at times. Cheers Garry

    1. Hello, Garry. Thank you for contacting us.

      If I understand your task correctly, the formula below should work for you:

      =IF(OR(B1=A1, OR(C1=A1, OR(D1=A1, OR(E1=A1)))), "OK", "NG")

      If the task is different, please describe it in more detail. I'll do my best to help you.

  6. Hi everybody,

    Can you please help me work out on this formula:

    I have the following table of an employee and I need to add 20 leaves on the
    anniversary date of joining of this employee i.e. every year on the 20th of June in his/her credit:

    Date of Joining: 20th June 2014 (dd/mm/yyy) 20/06/2014

    Duty Period Add 20 Leave Taken
    20/06/2015 25/07/2016 20 26/07/2016 30/07/2016
    31/07/2016 31/05/2017 0 - -
    01/06/2017 15/06/2017 20 16/06/2017 15/07/2017

    Column A1&B1 is the Duty period, C1 Add 20 or leave it blank, D1&E1 is the leave taken period. Row 1 shows Duty period and leave taken,Row 2 shows only duty period and since duty period does not fall in the range of anniversary date C1 will shows "0" or blank. In the 3rd row the anniversary date falls in the leave period so 20 is added.

    I have searched the whole of Internet but yet to find a solution for the same. A formula for the same will be much appreciated.

    Thanks in advance.

    John Sanil

  7. i want to know what will be the formula if i want to compute for example:

    value for A=P, B=P, and C=P, then it should be S
    and vice versa;

    and;

    value for A=P, B=P, and C=NP, then it should be S

    value for A=NP, B=NP, and C=P, then it should be

    or: if for the cell a,b,and c have at least more than 1P then it should be P; same with the NP if more than 1 then it should be NS

  8. How to create a IF formula for Cell that contains percentage between 95% - 100% will be stated as "ACTUAL", less then 95% will be stated as "DOUBLE", between 100% - 115% will be stated as "FIFTY PERCENT", above 115% will be stated as "NIL"

    1. Hi Virendra,

      Copy the following following formula in B2 (the answer cell):

      =(IF(A2>114.9%,"NIL",IF(AND(A2>=100%,A2=95%,A2<=100%),"ACTUAL",IF(A2<95%,"DOUBLE")))))

      Format cell A2 as percentage.

      The premise:
      94.9% and below = DOUBLE
      95% to 99.9% = ACTUAL
      100% to 99.9% = FIFTY PERCENT
      115% and above = NIL

      Format cell A2 as percentage.

      The only problem with this formula is that if nothing is mentioned in cell A2 the answer always displays "DOUBLE".

      Hope Svetlana Cheusheva or someone can improve upon the formula.

      Regards,
      John Sanil

      1. Dear Virendra
        Sorry the premise (100% to 99.9%)
        should read as: 100% to 114.9%.

        Rest all remain the same.

        John

  9. Can someone help with the formula as per below mentioned details

    If "C" column Budgeted figure is 40.83 & Actual in "D" column is 37.69 then the VAR in "E" column is -3.15 with 92% Achievement in "F" column and the weightage defined for 100% achievement is 35 Marks which is in "G" column.

    I am looking out the IF formula help that if "F" column is less then 79.99% then "0" marks, and if same is between 80% to 94.99% then half of the 100% weightage defined 35 marks that is 17.5, and if the scoring is between 95% to 100% then the Actual % of weightage defined like if 97% achieved then 97% of 35 marks that is 33.95 marks.

  10. Hi,
    please, i have strings in a cell "itdr_a0863_tempodisconti-rtg_it_tem_mth_300_bask" and i would just like to get the "bask" word what function will i use or what is the solution. Thanks

  11. Need help with correct formula for:

    Parcels less than 2kg in weight are charged at 0.365cents per gram
    Parcels over or equal to 2kg in weight are charged at 0.425cents per gram

    Thank you.

    1. Hello, Marie. Thank you for your question.

      Let's suppose that the parcels' weight is in A1.
      You put the following formula into B1 in order to get the charge for the parcel:

      =IF(A1<2, "0.365 cents per gram", IF(A1>=2, "0.425 cents per gram"))

      I hope this helps. Please let me know if you have any other questions or difficulties.

  12. P001 OW RF 4/23/2016 0:00
    P001 OW RF 4/23/2016 0:00
    P001 OW RF 4/23/2016 0:00
    P001 OW RF 4/20/2016 0:00
    P001 OW RF 4/19/2016 0:00
    P001 OW RF 4/19/2016 0:00
    P001 OW RF 4/19/2016 0:00
    P001 OW RF 4/19/2016 0:00

    I have this data in sheet1 whereas i have, the below data in sheet 2

    4/19/2016 0:00 4/20/2016 0:00 4/23/2016 0:00 4/24/2016 0:00
    P001

    i want a formula which can highlight corresponding cell in sheet 2 if the date against P001 occurs in sheet1.

    1. can anybody help pls.

  13. How to display, if( c2= a(alphabet) or b or c or d or e or f or g, the value is true , or value is false). Please help

    1. Hello,
      You can try the following formula:

      =IF(OR(C4="A", C4="B", C4="C", C4="D", C4="E", C4="F", C4="G"),TRUE, FALSE)

      In case you need the whole alphabet, use this one:

      =IF(OR(C2="A", C2="B", C2="C", C2="D",C2="E", C2="F", C2="G", C2="H", C2="I", C2="K", C2="L", C2="M", C2="N", C2="O", C2="P", C2="Q", C2="R", C2="S", C2="T", C2="V", C2="X", C2="Y", C2="Z"),TRUE, FALSE)

      I hope this helps. Please let me know if you have any other questions or difficulties.

  14. =IF(W7260$U$7276,"Late")))

  15. Good day

    Please can you assist me with my nested IF formula:

    =IF(W7260$U$7276,"Late")))

    If Cell reference W7260 is blank - I want to return a blank, how do accomplish this?

    Thank you

  16. Hi, Need desperate help with a formula.

    If column (J6:J21)contains the value (0.8) and if amount in column (K6:K21)is equal to or greater than 192 then cell turns yellow, or equal to or greater than 256 cell turns green, or equal to or greater than 320 cell then turns red.

    The same for if column (J6:J21)contains the value (0.5)equal to or greater than 120 then cell turns yellow, or equal to or greater than 160 cell turns green, or equal to or greater than 200 cell then turns red.

    Thankyou in advance for your help

  17. I'm tracking selling stuff and the site I'm using takes 20% commission for any item I sell above $14, anything $14 and under they take a $2.95 flat fee. To calculate this I'm using the equation:
    =IF(A3>14,A3*0.8,A3-2.95)

    However, since I'm putting this on things that haven't been sold yet, I only want there to be an output number if the item actually sold, otherwise I want it to be zero so I can add up the entire column without having some cells be "-2.95". The A3 cell in this example is blank because it hasn't sold yet. How would I do this?

  18. pl. help me with correct formula.

    example:

    =if(A1="apple",B1*4%),if(A1="animal",B1=10%),if(A1="tiger",B1*20%))

    pl. share correct formula to get the result,

    Thanks in advance

  19. Hi!
    Great website. I'm learning a good bit. Thanks.

    I'm trying to configure a simple budget. I have two columns: one is a category (e.g., "Groceries"). The second is the amount purchased.

    I want to Sum all "Groceries" formula. How would I do this?

    It would start like this...

    =IF(C2="Groceries",

    Thanks!
    Daniel

    1. Hello, Daniel. Thank you for your question.

      You can use the SUMIF function to configure your budget. Let's suppose that you have a table with types of your costs in column A and the amount purchased in column B. Please put the word "Groceries" in cell D2 and following formula in E2:

      =SUMIF(A2:B8, D2, B2:B8)

      You can learn more about this function in our blog article. I hope you'll find this information helpful.

      I hope this helps. Please let me know if you have any other questions. I'll be happy to help!

    2. you can use formula =sumif

  20. I need help - If a cell is $350,000 or less then I want another cell to come out to 0 - d6 is $350,000 then I want J15 to have 0 entered. Whereas, if I want D6 greater than $350,000 then I want J15 have $595.00 entered. I cannot figure this out at all. Any help would be appreciated. Thanks a bunch!

  21. I am trying to write a formula where 'x'=1 in 1 cell, then 'x'=0 in the 2nd cell, then 'x'=0.5 in the 3rd cell. (ie B6, C6, D6), and then I need (ie E6) to be the sum of B6,C6,D6.

    I am in desperate need of help. Please help!

  22. Inv. No. Cust Code
    390100209 800412
    390100210 800808
    390100211 801007
    390100212 801392
    390100213 801395
    390100214 801405
    390100215 801412
    390100216 801547
    390100217 802697
    390100218 802775
    390100219 802836
    390100220 803033
    390100221 803346
    390100222 800733
    390100223 800121
    390100224 800527
    390100225 800558
    390100226 800577
    390100227 800702
    390100228 800702
    390100229 800702
    390100230 800702
    390100231 800702

  23. I am trying to build a price list based on tiers. If C2 is less than 20, multiple it by two, if 20 to 99 multiple by 1.54%, if 99 to 599 multiple by 1.43, if 599 or more multiple by 1.34. Also C2 needs to have a tax rate of .08517 added to it but before commission markup.
    Ex: $20 x .08517 = 1.71 $20 x 2 = $40 plus $1.71 tax = $41.71.
    I keep getting errors.

    1. Hello, June,

      as for the first part of your task, you can use this nested IF:
      =IF(C2<20,C2*(1+2%),IF(AND(C2>=20,C2<99),C2*(1+1.54%),IF(AND(C2>=99,C2<599),C2*(1+1.43%),IF(C2>=599,C2*(1+1.34%),""))))

      As for the second part, could you please describe it in more details? Thanks

  24. I want to convert x into an absolute number. The value of "x" has to be 0 to 5. I have 3 different rows, (ie B6, C6 and D6). I then need total sum of these rows ie in E6.
    I have this formula so far, =IF(AND(ISBLANK(B4)=FALSE,B4>=0),IF(AND(ISBLANK(C4)=FALSE,CB42>=0),IF(AND(ISBLANK(D4)=FALSE,D4>=0),B4+C4+D4))) but I cannot figure out how to include the x=a number 0-5.
    Please help.

  25. Hi,

    I want to convert KB values and MB values into GB's, could you please help me in this .

    cells are filled like below
    eg : 19K, 1.0M
    I want to convert them into GB's

  26. hi i need vb code for
    if my value of sheet 1 "B" column value is same as sheet 2 "B" column value is correct and then sheet 1 "k"column value print in sheet 2 "K" column if anybody know please let me know

    1. Hi, Irfan,

      I'm sorry, we don't create VBA codes. Also, I'm afraid that your task is not entirely clear.
      I kindly suggest you to ask around MrExcel forum for the vba code you're looking for. Make sure to explain your task in more details.
      Thanks!

      1. THANK YOU NATALIA FOR YOUR RESPONSE

  27. hi i need vb code for
    if my value of sheet 1 b2 is same as sheet 2 b2 value is correct and then sheet 1 k2 value print in sheet k2 if anybody know please let me know

  28. I am trying to figure out what to do with VBA code and to get some kind of conditional function in Excel, but it doesn't seems to work.
    I have a list of articles in one column and a list of numbers in another column. What I want is to get the number display in the cell in third column but conditionally. Example: if A2=B2 then in the cell C2 I want the number which is in D2. But cells in A column are all set up as validations from E column. If something is not clear let me know please.
    Thank you!

  29. if(B5>20,B5<50,"20-50") How to work, please find like this

    1. Hello

      Try the following formula:
      =IF((AND(B5 > 20, B5 < 50)), "20-50", " " )

      You can also read more about IF formulas with multiple conditions here

      Feel free to contact us if you have any other questions.

      1. Hi,
        Please correct this,
        =if(A<B,B-A,IF(B=0,0,""))

  30. Great work and service to us Excel noobs :-)

    I have multiple sheets and my main dashboard is assigning a "word" rating based on the value from different sheets (from 0 to 1). But it is broken and I am at a loss - any help?

    =IF('sheet #1'!S21=",",IF(<=.20,"Not Effective",IF(<=.40,"Needs Improvement",IF(<=.60,"Generally Effective",IF(<=.80,"Effective",IF(<=1,Strong,"Error"))))))

  31. Hi could you please help me to apply if conditions with multiple results.

    A B C Result
    Transactions No Yes Client custom in both versions >Transactions
    Accounts No Yes Client custom in both versions > Accounts
    No Yes Client custom in both versions

    Cotains "History" No No This metadata appears only in V7 client
    database. Newly added metadata item under
    "History" folder.
    Cotains "1000INT BE"No No Interest BE added in V7

    Yes No New Standard Data item
    Yes Yes 712 Metadata items match with both 61 and
    712 Standard Template

  32. I want to get the difference of the two numbers but what if one of my cell is blank or have a text value?

    Example:
    My CELL A1 = 5
    CELL B1 = APPLE

    THANKS!

  33. Have different tables that have been joined into one spreadsheet
    table 1 has account open date, account ID#, & last updated
    table 2 has account ID, code, name, status, & update code

    Column A1 has an ID#
    Column B1 has a Code#
    Column C1 has a Name
    Column D1 has a Status
    Column E1 has an Update Code
    Column F1 has the Last Updated Date

    I have duplicate account IDs that are missing vital information.
    I would like to attach that information to row that contains the account open and account ID

    How can I get B2 & C2 into B1 & C1 if A1 = A2?

    1. Table 1
      ID Up Date
      18318ZE3-B96F-448F-8501-70625246C49E 20170404
      Table 2
      ID Code Name Stat UC
      18318ZE3-B96F-448F-8501-70625246C49E 1122 Jones A 1

      In Excel

      ID Code Name Stat UC Date
      18318ZE3-B96F-448F-8501-70625246C49E 20170404
      18318ZE3-B96F-448F-8501-70625246C49E 1122 Jones A 1
      18931ZE3-D97F-449A-8571-78452211D12G 20170301
      18898ZE3-K96F-446F-8581-70625246C49G 1123 Smith A 1
      18998ZE3-H96F-448L-8502-71625246C49A 20170404
      18998ZE3-H96F-448L-8502-71625246C49A 1124 Brown A 1

  34. Please help me to use this funtion

    Example:
    culum a colum B
    abc 10
    dfe 7

    I want to show that: If (abc & 10) > 6 "fhggh", If (dfe & 7) > 4 "ssjfj"

    Thanks

    1. Hello, Phun,

      if I understand your task correctly, this formula should do:
      =IF(AND(A1="abc",B1>6),"fhggh",IF(AND(A1="dfe",B1>4),ssjfj,""))
      If you mean something other than that, please explain your task in more details.

  35. Hai i have one doubt

    First cell contains "F"
    Second cell contains "S"
    Third cell contains "V"
    & Fourth cell contains "Absent"

    First Cell & Fourth cell is mark as "fail"

    and remaining second & Third cell is mark as "pass"

    please rectify the problem through IF CONDITION

    i am using this condition i.e. =IF(C2="F","FAIL","PASS")

    i am using this formula only "f" is shown as "fail" what about "absent"

    how to come both at a time "F" and "Absent" are "fail" please rectify

  36. Think i am missing something, or just being dumb here, but what i want is a blank space :/

    If i add 2 cells, and if the result is below 0 i want the answer cell to be blank so that it is not included in a cross reference.

    Example (i think) :P

    H23,H24 if H24<H23 then =Blank cell

  37. hi
    Good Morning
    I have required a value in cell in if and formula of other cell value in e5
    For Example i have value in a2 is 9320 and c3 is Ketan in have this both in and formula and i want to required d3 cell value in e5

  38. Hi Natalia,

    Any help with my excel problem?

  39. Hi Natalia,

    Thanks for the quick response.

    here is the actual scenarios:
    FIRST - a student obtained grades for three pieces of assignments as follow:
    9/10 (1), 80/100 (1) and 30/50 (1). These were input in excel as follows: C2=10, D2=100, E2=50; C4=9, D4=80, E4=30. The total assignment weight () is H4=3. Therefore, the formula I used to calculate the overall assignment % weight is =SUM((C4/C2*100)+(D4/D2*100)+(E4/E2*100))/H4, which gave a result of 76.67%/77%...perfect.

    SECOND - another student got the following grades for the same three pieces of assignments: Abs/10 (1), 50/100 (1) and 25/50 (1). The grades were input in excel as follows: C2=10, D2=100, E2=50; C5=Abs, D5=50, E5=25. The total assignment weight () is H5=2. My problem now is trying to use a IF statement or formula to calculate the student's overall assignment % weight, without the student being penalized for the 'Abs' (absent).

    Thanks again for the assistance.

  40. I need help with one of my formulas. I need to calculate an outstanding number of days when f2 is blank but I do not want a calculation when f2 has a value in the cell. My formula currently looks like this: =IF(ISBLANK(F2),DAYS360(E2,G2),G2-E2) the problem is when I input a date into f2, my calculation in h2 does not go away. I do not need it to be blank though. It could say "complete" if need be. e2 is the date submitted and g2 is =today(). If f2 is blank then h2 should be calculating how many days has passed since submission. When the task is complete I need the outstanding days to change to blank or "complete".

  41. Hi

    I need to asign a value to prices which falls in a specific range

    0-50000 = Level 1
    50001 - 300000 = Level 2
    300001 - 500000 = Level 3

    This is what i did, but that only gives a value for the first level.

    =IF(E2:E10<=50000,"Level 1", "Other Value")

    1. Hi, Johan,

      please try this formula:
      =IF((COUNTIF(A1:C1,"<=50000"))>0,"Level 1",(IF((COUNTIF(A1:C1,">=50001")-COUNTIF(A1:C1,">300000"))>0,"Level 2",(IF((COUNTIF(A1:C1,">=300001")-COUNTIF(A1:C1,">500000"))>0,"Level 3","")))))

      Please note that the first criterion will be checked at first, and if there's at least one cell in the range that falls under this criterion, the rest won't apply.

      If you need something other than that, please specify.
      Hope it helps.

  42. Hi,

    I am trying to create a spreadsheet to calculate high school grades using the weighted average and weighted percentage method.

    My problem is, I want to show that if a student is absent for 1 or 2 assignments or tests, they will not be penalized in their average assignment grade.

    How then can I write a IF function to show this calculation?

    Thanks for your usual assistance.

    1. Hi, Hamack,

      for us to be able to assist you with an approximate formula, please specify the names of the columns you have, what values are stored there, and how you indicate that the student was absent.

      Thanks!

  43. Hi, I'm creating a formula that once your if you change status to Complete in column B, then column C will provide the current date

    1. Hi, Nyleve,

      if the status is in B2 and you need the result to return into C2, here's a formula:
      =IF(B2="Complete",TODAY(),"")

      Then copy the formula down column C.
      Please note that if B2 doesn't contain "Complete", C2 will remain empty.

  44. I have two workbooks that I am trying to link together. The first workbook is Participants' Weight. The cell I am looking at is G2. If G2 is blank, I want to only add cells C3, E3, G3, and I3 on the second workbook (Nutrition and Fitness). If G2 has a value, I want it to add that value PLUS C3, E3,G3, and I3. Can you please help with my formula? This is what I have..

    =IF(ISBLANK('Participants'' Weight'!G2),=SUM('Nutrition & Fitness'!C3+'Nutrition & Fitness'!E3+'Nutrition & Fitness'!G3+'Nutrition & Fitness'!I3),=SUM('Nutrition & Fitness'!C3+'Nutrition & Fitness'!E3+'Nutrition & Fitness'!G3+'Nutrition & Fitness'!I3+'Participants'' Weight'!G2))

    1. Nevermind, I figured it out!

  45. =IFERROR(INDEX(Tbl_Task2[Activity Actual Start Date],MATCH($B4&X$1,Tbl_Task2[Activity ID]&Tbl_Task2[Task Name],0)),"")

    How do I make the dates come out as blanks using this formula?

  46. Hi, I'm trying to create a schedule for our workers using the schedule for our kids. I'm trying to figure out how to put in a formula that says " If Cell A3 on Sheet 1 equals "JohnDoe" then cell A2 on Sheet 1 will be copied to cell A2 on Sheet 3". Is there a way I can do this?

    1. =IF(Sheet1!A3="enter text here", Sheet1!A2, " ")

      Enter this formula into A2 on sheet 3. Where it says "Enter text here" you can either enter the text you want to use or take away the quotations and enter a cell like B2...

      Drag the formula all the wya down

  47. =IF(SQRT(BP22-(BS22/BS23))+0.5>=BY19,0.95,SUM(BY22))

    Hi,
    I want to generate an expression that basically states that if the number in cell BY22 is not greater than 0.95, then this figure is to be used. The expression is being placed into BY22.

    Please help!!!!

    Cheers
    Paul

  48. Hi, I have a spread sheet with validated filtered lists and I need help constructing a formula that will add 14 or 28 days to the current date for a smart target review depending whether I select 2 week review or 4 week review.
    the current date is displayed in cell A19, the two week review in G21 and the four week review in G22. the answer will appear in cell G20 and should display the result of todays date plus 14 or 28 days displayed as a date. thanks if you can help.

  49. Hi,
    I wantto select one value from a set of values which is there in column and if it matches to the logical statement, then it should take the corresponding row value as true result else some zero.

    SWG Dia with enamel, mm Area of bare conductor, mm^2 R/Km@20oC ohms Weight Kg/Km
    8 4.219 12.97 1.3 116
    9 3.8 10.51 1.6 94
    10 3.383 8.302 2.1 74
    11 3.068 6.818 2.5 61
    12 2.756 5.48 3.1 49.22
    13 2.441 4.289 4 38.56
    14 2.129 3.243 5.3 29.15
    15 1.92 2.627 6.6 23.64
    16 1.709 2.075 8.3 18.678
    17 1.501 1.589 10.8 14.313
    18 1.293 1.167 14.8 10.537
    19 1.082 0.8107 21.3 7.324
    20 0.978 0.6567 26.3 5.939
    21 0.874 0.5189 33.2 4.702
    22 0.77 0.3973 43.4 3.607
    23 0.665 0.2919 59.1 2.655
    24 0.612 0.2452 70.3 2.233
    25 0.561 0.2027 85.1 1.851
    26 0.505 0.1642 105 1.499
    27 0.462 0.1363 126.5 1.245
    28 0.417 0.111 155.3 1.014
    29 0.384 0.09372 184 0.8559
    30 0.351 0.07791 221.3 0.7121
    31 0.33 0.06818 252.9 0.6245
    32 0.307 0.0591 291.7 0.5408
    33 0.287 0.05067 340.3 0.465
    34 0.264 0.04289 402 0.3932
    35 0.241 0.03575 482.2 0.3281
    36 0.218 0.02927 589.1 0.2686
    37 0.198 0.02343 735.9 0.2202
    38 0.175 0.01824 945.2 0.1679
    39 0.152 0.0137 1,258 0.1262
    40 0.142 0.011675 1,477 0.1079
    41 0.132 0.00981 1,758 0.0908
    42 0.119 0.008107 2,127 0.075
    43 0.109 0.006567 2,626 0.061
    44 0.097 0.005189 3,323 0.0481
    45 0.086 0.003973 4,340 0.0369

    e.g SWG = 39, select 0.0137 as true else 0
    SWG = 40, select 0.011675 as true else 0

    Please give solution.

    Thanks & Regards
    Ajeet

  50. I am trying to get a spreadsheet to calculate a time penalty for a certain class in racing. The time penalty formula is =+IF(AND($A33="",$B33=""),"",IF($B$5=0,IF(K33="","",K33),IF(K33="","",IF($B33="A",K33*$B$5,K33)))), however if I enter a DNS or DNF (did not start/finish) in cell K33 then the formula comes up with a #value error because it expects a time value in K33.
    This only causes an issues where B33=A.
    So basically B5 is a percentage time penalty, column K is the actual time taken for the race and for a specific class of vehicle we need to add a time penalty.
    There may be 6 heats, one heat the vehicle may not have finished, so instead of a time entered it is DNF, but this will not multiply the time penalty. What I want is for the adjusted time column to show DNF as well but it won't because of the calculation it is trying to do

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)