Please see the example file attached. I needed dynamic formula to calculate Town wise Value for each product codes based on percentage of Town wise total / Total of all town. I have a very large data, shortened my data for example.
Please see the example file attached. I needed dynamic formula to calculate Town wise Value for each product codes based on percentage of Town wise total / Total of all town. I have a very large data, shortened my data for example.
Still using Excel 2010? Which are the EXPECTED results? If they are not there, where and what should they be? Give a worked example.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Please find the revised example file. I need result in column F to I. For Example I needed formula in cell F4 to calculate value of E4* (Town 1 Total of respective Product code from Column A, i.e. 400) / (Total Town Qty of respective Product code from Column A, i.e. 992). I needed formula which I can easily use on other cells from different Product codes. I'm using formula =$E4*($F$11/$E$11) in cell C4, but for that I needed to change formula for using it on F12.
I'm using Office 2010 and office 2013 in my office.
F4, copied across and down:
=$E4*INDEX(F5:F24,MATCH("Total",$B5:$B24,0))/INDEX($E5:$E24,MATCH("Total",$B5:$B24,0))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Thank you so much sir. That exactly what I wanted.
You're welcome!
Is there any way we get same results if entire Column B does not have any value. By using Max if function in column E and F:I based on product code in column A?
Only the first block has been done:
=$E4*INDEX(F4:F24,MATCH(TRUE,($A4:$A24<>$A4),0)-1)/INDEX($E4:$E24,MATCH(TRUE,($A4:$A24<>$A4),0)-1)
Thank You so much sir. SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks