Posts by etaf

    Re: Calculate 20% and/or 40% Tax depending on salary


    =IF(AND($O$7>$R$5,$O$7<=$S$5),($O$7-$S$4)*0.2,$T$5*0.2)


    so that test to see if sal > 34371 and less then 150000
    which it is 36000
    then you do
    ($O$7-$S$4)*0.2
    which is the difference of the 34371 and the Sal
    BUT that should be @ 0.4 not 0.2
    and if false you do this $T$5*0.2 - not sure why ?


    if its not greater than 34371- then false would = 0

    Re: Error in Excel Formula


    thanks for letting us know


    you still dont have a condition for the last FALSE element




    to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.



    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Re: Error in Excel Formula


    what are we reviewing for
    you seem to be using and as i posted


    you need to add the final false statement


    if you add my formula - you will see that none of your conditions apply


    q22 - does not allow the yes or no ?

    Re: Error in Excel Formula


    you need to clarify what you are trying to do as requested by exceleb


    looks like you are looking for an and
    =IF(Q22="Yes",Q149="Existing",SUM(Q29+45),IF(Q22="NO",Q149="Existing",SUM(Q29+1),IF(Q22="NO", Q149="NEW",SUM(Q29+365),IF(Q22="NO",Q149="Transfer",SUM(Q29+90))))


    so if Q22 ="yes" AND Q149 = "existing" then do SUM(Q29+45)


    =IF(AND(Q22="Yes",Q149="Existing"),SUM(Q29+45),IF(AND(Q22="NO",Q149="Existing"),SUM(Q29+1),IF(AND(Q22="NO",Q149="NEW"),SUM(Q29+365),IF(AND(Q22="NO",Q149="Transfer"),SUM(Q29+90),"What todo if conditions do not exist"))))


    but YOU need to add a false - if all these do not work


    Re: Conditional formatting dates in excel


    you would put those formulas into conditional format and change the fill colour


    for the blank cell, there is a difference between a blank cell and an empty cell, but this will work on both
    = Cell = ""
    format Red


    then additional formulas for the date
    =IF( TODAY() - 20 <= CELL, TRUE,FALSE)
    not sure what you are doing with workday - if you could explain

    Re: Sheet Name as Variable


    you maybe able to use indirect and use the cell value in column b


    =INDIRECT( "'[data.xlsx]"&B2&"'!$l$10")
    then when you copy down the sheet B2 will change to B3,B4 - which will call sheet 01,02, 03 etc


    OR you can use it in the formula

    Re: Perform multiple calculations when a value goes over a threashold


    i think this formula should do what you are after - do a little bit of testing


    =IF(SUM(K$3:K4)<=10000,K4*H$3,IF(SUM(K$3:K3)>10000,K4*I$3,((10000-SUM(K$3:K3))*H$3)+(K4-(10000-SUM(K$3:K3)))*I$3))


    in L3 the 1st entry just use miles*rate =K3*H3
    then in L4 - put the formula and copy down


    =IF(SUM(K$3:K4)<=10000,K4*H$3,IF(SUM(K$3:K3)>10000,K4*I$3,((10000-SUM(K$3:K3))*H$3)+(K4-(10000-SUM(K$3:K3)))*I$3))


    the 1st section is testing to see if the total mileage including this periods is less than or equal to 10000 - if so then use the H3 rate


    =IF(SUM(K$3:K4)<=10000,K4*H$3,IF(SUM(K$3:K3)>10000,K4*I$3,((10000-SUM(K$3:K3))*H$3)+(K4-(10000-SUM(K$3:K3)))*I$3))


    the 2nd section is testing is the mileage upto last period more than 10000 - if it is then this period will be more than 10000 so use the I3 rate


    =IF(SUM(K$3:K4)<=10000,K4*H$3,IF(SUM(K$3:K3)>10000,K4*I$3,((10000-SUM(K$3:K3))*H$3)+(K4-(10000-SUM(K$3:K3)))*I$3))


    the 3rd section must therefore be across 10000 and the mileage should be at the two different rates
    ((10000-SUM(K$3:K3))*H$3)
    takes 10000 away from the mileage upto last period - and so works out how many miles this period are at the < 10000 rate
    (K4-(10000-SUM(K$3:K3)))
    takes the miles for this period and takes away from the 10000 - the mileage upto last period to work out the miles over 10000



    I have played with your sample - and just added miles - so column K does not have your formula in all cells

    Re: NESTED IF formula with DATE FUNCTION


    well for some reason march is not correct
    =IF(G$5<=$E6 which is true and so should return
    [TABLE="width: 64"]

    [tr]


    [TD="width: 64, align: right"]41040[/TD]

    [/tr]


    [/TABLE]
    6840 * 6


    the test for the dates is not working
    i copied into a new cell and not working


    when i re-enter the dates and values in a different section it works


    for march it is doing a6*4


    but March is less than contract due date


    31/3/13 is less than 27/8/13


    cant seem to make it work in your sheet - but can on a separate entry


    see d20 and g20 and g22


    tested if text and used values - copied formula into notepad and reentered


    must be going made
    i'll have another play

    Re: Perform multiple calculations when a value goes over a threashold


    can you provide a sample spreadsheet
    also i dont fully understand what are you are trying to do

    Quote

    as an example if the limit is 100 and the running total is at 98, this months total is 10, the fisrt 2 would need to be multiplied by x and the remainder multipled by y and added together.

    not sure what the first 2 refers to - or the remainder of what


    need a further detailed explanation and examples for me to be able help here

    Re: Find lowest value in column B for each customer in col A w/multiple records per c


    you should be able to use a pivot table to do that easily


    also
    assuming the customer ID is in column A - starting A2
    and the values to find the minimum are in column B starting B2


    in cell d2 put
    =INDEX($A$2:$A$38, MATCH(0, COUNTIF($D1:D$1, $A$2:$A$38), 0))
    use control+shift+enter to make an array formula - {} appear around the formula
    copy down D
    this will extract a set of unique IDs for all customers in column A
    now in E2
    put
    =MIN(IF(A2:A38=D2,B2:B38))
    this will calculate the minimum for each unique customer ID in D
    copy down E


    see attached


    we can also add error correction to remove the 0 and #n/a if this works for you


    so that covers the minimum value by customer


    now a little confused on the rest

    Quote

    Find lowest value in column B for each customer listed from among many records for each customer and Identify which record has the least value

    sounds like the same thing here - Lowest value and least value by customer


    Quote

    Order # of column C had the least value

    is this by customer or lowest value in the whole list and the order number - simple Min and then a vlookup would do that for order # and flag


    but confirm your exact requirements before i spend any further time on something thats not required

    Re: TRYING TO MAKE A BONUS COMMISION PAYOFF calculation USING A GRAPH (VLOOKUP POSSIB


    i have put the lookup values in sheet 2
    A1 = $ Amount sold
    B1 = Qty


    and in c1
    =IF(ISERROR(INDEX(Sheet1!C3:L52, MATCH(B2,Sheet1!B3:B52,1), MATCH(A2,Sheet1!C2:L2,1))),0,INDEX(Sheet1!C3:L52, MATCH(B2,Sheet1!B3:B52,1), MATCH(A2,Sheet1!C2:L2,1)))


    I have also used the nearest so
    7999999 will use the 7000000 column


    this is an explanation of how the grid lookup works
    http://skp.mvps.org/xl00002.htm