Dear experts
I have some range data column wise date and month nos i want calculate redeemed points of month.
month nos like 3,6,12,24,36 i want find how many times of 24 & 36 if its first time 5 points *month,for second time 6 points*month if come more than twice i want give 12 points * Month, till end of column right.rest of ,3,6,12 no points.
find the attachment i want formula in where ever find redeemed value
b5 =if(OR(B4=24,B4=36),VLookup(countif($B$4:B$4,B$4),$A$22:$B$33,2,0)*B$4/12,B$4) and drag to the right.
in a22: b33 are the value 1 - 12 => 5 / 6 /12
See the attached file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Hi -
Rather than IF AND OR functions, I took a little different approach and changed your table into a matrix (see the shaded area in the attached spreadsheet), then I used INDEX/MATCH to pull the correct percentage from the matrix. That way, it's easy to expand to add more conditions. What you have now can be managed with IF AND OR, but if you add many more combinations, the formula gets really long and hard to debug.
See the attached file for a different approach to the solution.
Hope this helps.
____________________________________________
If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved
If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.
Oops - Please disregard. I posted this solution to the wrong thread! Sorry!
thanks alot sir It works i want with out vlookup table range can i refer the table in formula instead of a22:b33 becoz this is report belong to client i cant add any data range in workbook.
Why not?becoz this is report belong to client i cant add any data range in workbook.
You can use another sheet for that if you don't want to put it on the same sheet.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks