Posts by aussiformula

    Re: Nested IF Statement Alternative


    Hello Crush Drinker,


    Thanks again for getting back to me.


    Okay I will explain J22-J26.


    Some of the benefits payable have three (3) possible totals to check. (that have to have a balance left to provide any benefit)


    For eg.


    If a family limit exists (if there is an amount in the family balance above 'zero'). then the individual limit has has to be verified against this family limit, no benefits can be payable unless the family limit is more or equal to the individual limit(s), based on the letter attached to the particular individual So, if there is no family balance then I need a rule that ignores this check/validation entirely


    If regards to the other limit(s) J22-J26 this needs to be based on a a range of item(s), 800-850 which comes under the category of "Orthodontics'. Orthodontics has an individual lifetime limit (per person), which (if applicable) has to be (again based on the individual limit (applicable letter G-K) & possibly the family limit.


    So the sum up an individual (if claiming for an orthodontic item in the item range), would have to have remaining lifetime limit, family limit (if family limit has a balance to check (via code "f" if entered manually during the data entry), & individual limit also.


    If the item range is not in the Orthodontics category, the lifetime limit & validation would be ignored as would not be applicable


    Only the family & individual limits would be checked/validated being letter F & letter A-E


    I hope this explains it


    Thank you


    Thanks

    Hello,


    I have reached the 'nested if statement limit' & I agree maybe using "Vlookup" maybe a better option.


    Just not sure how to write the formula


    Thanks


    The eight scenarios are as follows:


    1) Takes the quote & results in a benefit at 60% of quote
    2) Takes the quote & results in a benefit at 80% of quote
    3) Takes the quote & results in a benefit of either 70% of the quote or the schedule amount (whichever is lower) x quantity
    4) Takes the quote & results in a benefit of either 75% of the quote or the schedule amount (whichever is lower) x quantity
    5) Takes the quote & results in a benefit of either 80% of the quote or the schedule amount (whichever is lower) x quantity
    6) Takes the quote & results in a benefit of either 75% of the quote or $98.00 per item (whichever is lower) x quantity
    7) Takes the quote & results in a benefit of either 75% of the quote or $350.00 per item (whichever is lower) x quantity
    8) Takes the schedule amount & results in a benefit x quantity x schedule fee

    Re: Countif or Array Formula


    Hello & Thank you


    Appreciate the last part of the formula, that was very helpful.


    My main question though that I think may have been overlooked is the following:


    Okay a few examples


    I have four (4) ranges as follows:
    These ranges are all text based as although they are a number there is a 'zero' at the front & I don't know how to tell excel to treat them as a number but having a leading zero
    011-086 category "Diagnostic"
    511-597 category "Restorative"
    111-171 category "Preventative"
    911-972 category "General"


    What formula would I use to validate against the applicable category against that FULL range of numbers?


    If they are to stay as numbers, how do I tell excel I want a leading zero? Do I have to leave the number as text?


    Thanks again







    Hello,


    I hope my query is a very easy one to create a solution.


    I have a column of data. The data is not a number but a series of text from '011-020 & maybe 5 other range(s)/criteria of similar text, which would be in the same formula


    I would like a countif or array formula that results in the text "Cleaning" to be created on an adjacent column if the cell contains any of the text in that range or blank as result if not


    I than have a balance $ say 500.00 (in another cell) that I want to be reduced if that criteria above is valid/true. This balance is in another cell & there is another calculation result being conducted that gives a total on another column that this total would subtract from say column 'e' with a total amount used of $300.00 (this total is based another formula in the workbook), so the total based on the criteria above would now be showing as $200.00 balance


    To add another criteria to this, there maybe five separate totals which are consisting of 5 difference individuals, & would setup within the above formula another criteria such as person A, B, C, D & E to be validated on as well as the item number, criteria & category. The categories maybe 'cleaning, crowns, x-rays, dentures, etc


    Thank you



    [TABLE="width: 949"]

    [tr]


    [td]

    Quantity

    [/td]


    [td]

    Item number(s)

    [/td]


    [td]

    Code category

    [/td]


    [td]

    Code family

    [/td]


    [td]

    Code person

    [/td]


    [td]

    Code benefit

    [/td]


    [td]

    Item quote

    [/td]


    [td]

    Item benefit

    [/td]


    [td]

    Total benefit

    [/td]


    [td]

    Conditions of benefit

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    1.00

    [/td]


    [td]

    011

    [/td]


    [td]

    Scaling

    [/td]


    [td][/td]


    [td]

    A

    [/td]


    [td]

    2

    [/td]


    [td]

    $ 41.00

    [/td]


    [td]

    $ 30.00

    [/td]


    [td]

    $ 30.00

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    A

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [td]

    Balance from WHICS

    [/td]


    [td]

    Balance (after benefits)

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Total

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]$41.00[/TD]

    [td][/td]


    [TD="align: right"]$30.00[/TD]

    [td]

    A Limits(s) remaining (

    [/td]


    [td]

    $ 300.00

    [/td]


    [td]

    $ 270.00

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Lump sum

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    B Limits(s) remaining (

    [/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [TD="colspan: 6"]Percentage return[/TD]
    [TD="colspan: 2"]73%[/TD]

    [td]

    C Limits(s) remaining (

    [/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    D Limits(s) remaining (

    [/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    OOP

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="colspan: 2"]$11.00[/TD]

    [td][/td]


    [td]

    E Limits(s) remaining (

    [/td]


    [td]

    $ -

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    F Limits(s) remaining family

    [/td]


    [td]

    $ 500.00

    [/td]


    [td]

    Not applicable

    [/td]


    [/tr]


    [/TABLE]

    Hello Team,


    I have attached my file which I want to be able to have several tests on a few different ranges of text &/or number(s) which tests the data, gives a result of 'text' {based on the relevant range, there maybe a few range(s) & based on the text result performs a calculation of the applicable balance left on the individual limit(s). Is it possible to run this test on multiple individual limit(s)? How would I structure the actual calculation so that the rows are validated & then link into the individual's limit(s) I guess I would need a code per person to link to the limit also? i.e row 1 code A & limit A


    Also want the 'family' limit to only be validated if there is a test done on another code/cell which tests if a family limit should be applicable based on this code


    Thank you

    Re: IF Statement


    Hello,


    I have attached my test file,


    The idea of the file is to validate the data based on the code i.e E2, E3, SK, etc, then I have an if statement that needs to test if D3 (the total quote) is less than the sum of A3*E3 (the item benefit times the quantity of the item, manually entered) then the total benefit needs to be 80% of D3 (the quote) or whatever percentage I have in the formula


    I also want the benefit column to validate against the total down the bottom (which is $1,200.00 or whatever I have entered there)


    If the benefit is equal to or less than the total limit (down the bottom) then the total accrued/totalled benefit needs to be zero once it reaches this limit (in the benefit column)



    Hope this makes sense


    Thanks

    Re: IF Statement


    =IF(C3="E2",IF(E3>0,F3,0),IF(C3="E3",IF(E3<D3*0.8,F3,D3*0.8),IF(C3="LC",IF(F3<=D3,F3,D3*0.8),IF(C3="SK1",IF(E3<=D3,98*A3,D3*0.75),IF(C3="SK2",IF(F3<=D3,F3,D3*0.8))))))
    Hello I have the above if formula which validates based on text being E2, E3, LC SK1 or SK2. it provides a result in this cell that this formula is inside. I would like to know how I can create one last validation that says if the result in this cell is equal to or greater than the amount of another cell (which contains a specific manually entered $ amount), then limit the total of this result to that cells total, e.g the formula is creating a result of $2,174.00 based on the results of SK1 validation rules, the amount in the other cell is $2,000.00. I want the result to never be allowed to be more than $2,000.00 & be limited to this total in the other cell

    Re: IF Statement


    Hello,


    Thank you for the formula. Just one thing it needs to only run this if statement if the Spec Code is set to "SPEC2"


    Much appreciated

    Hello.


    I am trying to create an IF statement which will validate the data in one cell & then check the data against another rule, if the second rule is true then ignore the first rule


    For example if criteria exists in the first cell ie certain letters of text then take the sum of another cell & provide the sum (which is 75% of the amount in that cell & at the same time check to make sure the result is not more than a certain amount, it is is then ignore the first result/validation & use the second result/validation)



    [TABLE="width: 655"]

    [tr]


    [td]

    Quantity

    [/td]


    [td]

    Item number(s)

    [/td]


    [td]

    Spec Code

    [/td]


    [td]

    Item Charge/Quote

    [/td]


    [td]

    Total Charge

    [/td]


    [td]

    Rebate (as per schedule)

    [/td]


    [td]

    Extended Rebate

    [/td]


    [td]

    Conditions

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    881

    [/td]


    [td]

    SPE2

    [/td]


    [TD="align: right"]$200.00[/TD]
    [TD="align: right"]$400.00[/TD]
    [TD="align: right"]$20.00[/TD]

    [td]

    $ 300.00

    [/td]


    [td]

    SP 70% up-to $98.00 per item

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]$20.00[/TD]
    [TD="align: right"]$20.00[/TD]
    [TD="align: right"]$10.00[/TD]

    [td]

    $ 10.00

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]$20.00[/TD]
    [TD="align: right"]$0.00[/TD]
    [TD="align: right"]$0.00[/TD]

    [td]

    $ -

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]$20.00[/TD]
    [TD="align: right"]$0.00[/TD]
    [TD="align: right"]$0.00[/TD]

    [td]

    $ -

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]$20.00[/TD]
    [TD="align: right"]$0.00[/TD]
    [TD="align: right"]$0.00[/TD]

    [td]

    $ -

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]$20.00[/TD]
    [TD="align: right"]$0.00[/TD]
    [TD="align: right"]$0.00[/TD]

    [td]

    $ -

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]$20.00[/TD]
    [TD="align: right"]$0.00[/TD]
    [TD="align: right"]$0.00[/TD]

    [td]

    $ -

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]$20.00[/TD]
    [TD="align: right"]$0.00[/TD]
    [TD="align: right"]$0.00[/TD]

    [td]

    $ -

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]$0.00[/TD]
    [TD="align: right"]$0.00[/TD]
    [TD="align: right"]$0.00[/TD]

    [td]

    $ -

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]$0.00[/TD]
    [TD="align: right"]$0.00[/TD]
    [TD="align: right"]$0.00[/TD]

    [td]

    $ -

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]$0.00[/TD]
    [TD="align: right"]$0.00[/TD]
    [TD="align: right"]$0.00[/TD]

    [td]

    $ -

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]$0.00[/TD]
    [TD="align: right"]$0.00[/TD]
    [TD="align: right"]$0.00[/TD]

    [td]

    $ -

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]$0.00[/TD]
    [TD="align: right"]$0.00[/TD]
    [TD="align: right"]$0.00[/TD]

    [td]

    $ -

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]$0.00[/TD]
    [TD="align: right"]$0.00[/TD]
    [TD="align: right"]$0.00[/TD]

    [td]

    $ -

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]$0.00[/TD]
    [TD="align: right"]$0.00[/TD]
    [TD="align: right"]$0.00[/TD]

    [td]

    $ -

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"]$0.00[/TD]
    [TD="align: right"]$0.00[/TD]
    [TD="align: right"]$0.00[/TD]

    [td]

    $ -

    [/td]


    [td]


    [/td]


    [/tr]


    [/TABLE]




    Hello Just to clarify Cell with quantity is A, Item number is B, Spec Code is C, Item Charge/Quote is D, Total charge is E, Rebate as per schedule is F, Extended rebate is G, Conditions is H & Limits is I



    So my original question is I have an if statement which is in G which which takes the sum which calculates 70% of E. The first code in C creates a condition in H. At the moment I have result being created in G based on E 70% I want the result in G to be limited to no more than $98.00 even if the result is more an than 70% of E

    Re: Year &amp; Date


    Hello,


    I previously requested assistance with a formula


    =IF(DATE(YEAR(EDATE(A2,31*12)),7,1)=your criteria, do this, do that) & =DATE(YEAR(EDATE(H4,31*12)),7,1)
    which took a date (being date of birth) adding 31 years it & rounding the 31 years from the date of birth to the 1st of July of the applicable 31st year of birth
    I then wanted an if formula which conducted a test on the result & resulted in the following
    If the result is less than the 01/07/2000 then make the result 01/07/2000, i.e their 31st birthday was prior to 1st July 2000
    If the result is greater than 01/07/2000 the make the result 1st of July of the 31st Year (i.e if the person's 31st birthday fell after the year 2000 use 1st July of their 31st birth year as the result date


    Hope that makes sense


    Thanks

    Hello,


    I have a simple question


    I have a formula which takes a person's DOB adds 31 years to it then gives a result


    I would like the formula (probably an 'if' statement which says if the result (new date in the future) is less than a particular/specific date (in this case the 1st of July 31 years from the DOB) how could I create a formula that does this?


    Eg 31 years from person's DOB, the same number of years but automatically changes to the 1st of July of the calculated year, so conditions apply to the date & month only, the year not to change


    Thanks