SOLVED

Need help with formula in excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1328343%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20formula%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1328343%22%20slang%3D%22en-US%22%3E%3CP%3EI%20had%20earlier%20sought%20help%20and%20most%20of%20my%20issues%20were%20resolved.%20However%2C%20I%20had%20not%20saved%20it%20in%20proper%20place%20and%20therefore%20seeking%20help%20once%20again.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20having%20issue%20with%20with%20calculation%20in%20column%20'K'%20.%20if%20column%20'G'%20is%20more%20then%2010%26gt%3B%20then%20calculate%20at%20the%20rate%2C%20and%20if%20column%20'G'%20is%2031%26gt%3B%20then%20calculate%20at%20the%20rate%2C%20so%20on%20and%20so%20forth.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20to%20remember%20some%20formula%20which%20was%20shared%20earlier%2C%20but%2C%20I%20may%20have%20not%20placed%20it%20in%20order.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%2C%20I%20have%20attached%20a%20work%20sheet%20for%20ready%20reference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1328343%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1328528%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20formula%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1328528%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F295505%22%20target%3D%22_blank%22%3E%40Ronald1969%3C%2FA%3E%26nbsp%3B%20there%20are%20a%20number%20of%20ways%20to%20do%20this.%26nbsp%3B%20The%202%20that%20come%20to%20mind%20are%3A%3C%2FP%3E%3CP%3E%3Difs(G%26lt%3B10%2C%20%5Bdo%20this%5D%2C%20G%26lt%3B20%2C%20%5Bdo%20this%20instead%5D%2C%20%E2%80%A6%3C%2FP%3E%3CP%3E%3Dchoose(1%2B(G%26gt%3B10)%2B(G%26gt%3B20)%2B...%2C%20%5Bdo%20this%20if%20%26lt%3B10%5D%2C%20%5Bdo%20this%20%26lt%3B20%5D%2C%20%E2%80%A6%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1329513%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20formula%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1329513%22%20slang%3D%22en-US%22%3EIs%20it%20possible%20to%20show%20one%20or%20two%20example%20in%20the%20excel%20attachment%20which%20is%20with%20my%20post.%20specially%20if%20days%20are%20more%20then%2010%2C%20and%20more%20then%2031.%20Earlier%2C%20before%20the%20post%20when%20I%20tried%2C%20I%20was%20getting%20error%20messages%20%22%20Value%22%20%22%23%23%23%22%20and%20some%20other%20errors.%20The%20idea%20is%2C%20for%20first%2010%20days%20the%20amount%20gets%20calculated%20400*number%20of%20days%20and%20after%2011th%20day%20to%2031st%20day%2C%20the%20calculation%20is%2012000%2F31*number%20of%20days%20(Roundup)%20and%20thereafter%20add%20400%20for%20each%20extra%20day%2C%20if%20any.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1330437%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20formula%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1330437%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F295505%22%20target%3D%22_blank%22%3E%40Ronald1969%3C%2FA%3E%26nbsp%3B%20I%20don't%20know%20if%20that%20is%20really%20what%20you%20want.%26nbsp%3B%20going%20from%20day%2010%20to%20day%2011%20you%20have%20a%20major%20drop%20by%20'back%20calculating'%20all%20the%20previous%20days%20at%20the%20lower%20rate.%20%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20what%20I%20think%20you%20are%20asking%20for%3A%3C%2FP%3E%3CP%3E%3CFONT%3E%3DROUND(CHOOSE(1%2B(G7-10%26gt%3B0)%2B(G7-31%26gt%3B%3D0)%2CG7*400%2CG7*12000%2F31%2C12000%2B(G7-31)*400)%2C-1)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3Ebasically%20if%20%26lt%3B%3D10%20then%20choose%20%231%20which%20is%20G7*400%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3Eif%20%26gt%3B10%20but%20%26lt%3B31%20then%20choose%20%232%20which%20is%20G7*12000%2F31%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3Eif%20%26gt%3B%3D31%20then%20start%20with%20a%20base%20of%2012000%20and%20add%20400%20for%20each%20day%20%26gt%3B31%20(G7-31)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3Ebut%20shouldn't%20you%20instead%20only%20appy%20the%20lower%20wage%20rate%20for%20the%20days%26gt%3B10%3F%26nbsp%3B%20and%20hence%20would%20be%3A%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3E%3DROUND(CHOOSE(1%2B(G7-10%26gt%3B0)%2B(G7-31%26gt%3B%3D0)%2CG7*400%2C4000%2B(G7-10)*12000%2F31%2C12130%2B(G7-31)*400)%2C-1)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3Ein%20this%20case%20if%20%26gt%3B10%20but%20%26lt%3B31%20then%20start%20with%204000%20from%20day%2010%20and%20add%2012000%2F31%20for%20each%20additional%20day%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3Ein%20case%20%26gt%3B%3D31%20then%20start%20with%2012130%20(the%20amount%20earned%20up%20to%20day%2031%20and%20add%20400%20for%20each%20additional%20day%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3Ebut%20of%20course%20then%20the%20days%20%26gt%3B31%20would%20also%20have%20that%20slightly%20higher%2012130%20starting%20point%20instead%20of%2012000.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

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.

 

3 Replies
Highlighted
Best Response confirmed by Ronald1969 (Contributor)
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], …

 

Highlighted
Is it possible to show one or two example in the excel attachment which is with my post. specially if days are more then 10, and more then 31. Earlier, before the post when I tried, I was getting error messages " Value" "###" and some other errors. The idea is, for first 10 days the amount gets calculated 400*number of days and after 11th day to 31st day, the calculation is 12000/31*number of days (Roundup) and thereafter add 400 for each extra day, if any.
Highlighted

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