Apr 22 2020 04:27 AM
I had earlier sought help and most of my issues were resolved. However, I had not saved it in proper place and therefore seeking help once again.
I am having issue with with calculation in column 'K' . if column 'G' is more then 10> then calculate at the rate, and if column 'G' is 31> then calculate at the rate, so on and so forth.
I have tried to remember some formula which was shared earlier, but, I may have not placed it in order.
Please help, I have attached a work sheet for ready reference.
Apr 22 2020 05:34 AM
Solution@Ronald1969 there are a number of ways to do this. The 2 that come to mind are:
=ifs(G<10, [do this], G<20, [do this instead], …
=choose(1+(G>10)+(G>20)+..., [do this if <10], [do this <20], …
Apr 22 2020 09:39 AM
Apr 22 2020 02:46 PM
@Ronald1969 I don't know if that is really what you want. going from day 10 to day 11 you have a major drop by 'back calculating' all the previous days at the lower rate.
Here is what I think you are asking for:
=ROUND(CHOOSE(1+(G7-10>0)+(G7-31>=0),G7*400,G7*12000/31,12000+(G7-31)*400),-1)
basically if <=10 then choose #1 which is G7*400
if >10 but <31 then choose #2 which is G7*12000/31
if >=31 then start with a base of 12000 and add 400 for each day >31 (G7-31)
but shouldn't you instead only appy the lower wage rate for the days>10? and hence would be:
=ROUND(CHOOSE(1+(G7-10>0)+(G7-31>=0),G7*400,4000+(G7-10)*12000/31,12130+(G7-31)*400),-1)
in this case if >10 but <31 then start with 4000 from day 10 and add 12000/31 for each additional day
in case >=31 then start with 12130 (the amount earned up to day 31 and add 400 for each additional day
but of course then the days >31 would also have that slightly higher 12130 starting point instead of 12000.
Apr 22 2020 05:34 AM
Solution@Ronald1969 there are a number of ways to do this. The 2 that come to mind are:
=ifs(G<10, [do this], G<20, [do this instead], …
=choose(1+(G>10)+(G>20)+..., [do this if <10], [do this <20], …