Posts by etaf

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


    so that test to see if sal > 34371 and less then 150000
    which it is 36000
    then you do
    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


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


    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


    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


    the 3rd section must therefore be across 10000 and the mileage should be at the two different rates
    takes 10000 away from the mileage upto last period - and so works out how many miles this period are at the < 10000 rate
    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"]


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


    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


    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

    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
    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


    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


    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


    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