Re: Nested IF Statement Alternative
Hello Crush Drinker,
Just so you know I didn't know anything about excel I have created some formulas trying to do what I want them too, but I think you may have a better way to create the desired result
Thanks again
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
Re: Nested IF Statement Alternative
Hello Crush Drinker,
Thank you for getting back to me.
The formula is great but I am unsure how to edit it for a few other scenarios
Are you able to assist/update for me?
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
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
Quote from crushdrinker06;769671Display MoreYou said
Do you mean that if the cell is blank then the adjacent cell would be blank else put "Cleaning"? What would be
As far as
You could use the formula =if(CleaningCell = "Cleaning", TotalCell - Reduction, Total Cell)
If the different have different reduction amounts then you could set up a table with the different reduction amounts and change the above formula to nested ifs.
=if(CleaningCell = "Cleaning", TotalCell - CleaningReduction, if(CleaningCell = "x-rays", TotalCell - X-raysReduction,if(CleaningCell = "dentures", TotalCell - DenturesReduction,if(CleaningCell = "etc", TotalCell - EtcReduction,Total Cell))))
I hope this helps, and if you can give more clarification to the I might be able to give you a more complete answer.
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"]
Quantity
[/td]Item number(s)
[/td]Code category
[/td]Code family
[/td]Code person
[/td]Code benefit
[/td]Item quote
[/td]Item benefit
[/td]Total benefit
[/td]Conditions of benefit
[/td]1.00
[/td]011
[/td]Scaling
[/td]A
[/td]2
[/td]$ 41.00
[/td]$ 30.00
[/td]$ 30.00
[/td]A
[/td]$ -
[/td]$ -
[/td]$ -
[/td]$ -
[/td]$ -
[/td]$ -
[/td]$ -
[/td]$ -
[/td]$ -
[/td]Balance from WHICS
[/td]Balance (after benefits)
[/td]Total
[/td]
[TD="align: right"]$41.00[/TD]
[TD="align: right"]$30.00[/TD]
A Limits(s) remaining (
[/td]$ 300.00
[/td]$ 270.00
[/td]Lump sum
[/td]B Limits(s) remaining (
[/td]$ -
[/td]
[TD="colspan: 6"]Percentage return[/TD]
[TD="colspan: 2"]73%[/TD]
C Limits(s) remaining (
[/td]$ -
[/td]D Limits(s) remaining (
[/td]$ -
[/td]OOP
[/td]
[TD="colspan: 2"]$11.00[/TD]
E Limits(s) remaining (
[/td]$ -
[/td]F Limits(s) remaining family
[/td]$ 500.00
[/td]Not applicable
[/td]
[/TABLE]
Re: If statement
Hello,
I have re-done the worksheet, with scenarios for you
Thank you
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
Re: IF Statement
Hello,
Have submitted test data as requested
Look forward to your reply
Tim
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"]
Quantity
[/td]Item number(s)
[/td]Spec Code
[/td]Item Charge/Quote
[/td]Total Charge
[/td]Rebate (as per schedule)
[/td]Extended Rebate
[/td]Conditions
[/td]2
[/td]881
[/td]SPE2
[/td]
[TD="align: right"]$200.00[/TD]
[TD="align: right"]$400.00[/TD]
[TD="align: right"]$20.00[/TD]
$ 300.00
[/td]SP 70% up-to $98.00 per item
[/td]1
[/td]
[TD="align: right"]$20.00[/TD]
[TD="align: right"]$20.00[/TD]
[TD="align: right"]$10.00[/TD]
$ 10.00
[/td]
[TD="align: right"]$20.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
$ -
[/td]
[TD="align: right"]$20.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
$ -
[/td]
[TD="align: right"]$20.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
$ -
[/td]
[TD="align: right"]$20.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
$ -
[/td]
[TD="align: right"]$20.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
$ -
[/td]
[TD="align: right"]$20.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
$ -
[/td]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
$ -
[/td]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
$ -
[/td]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
$ -
[/td]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
$ -
[/td]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
$ -
[/td]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
$ -
[/td]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
$ -
[/td]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
$ -
[/td]
[/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 & 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
