I have 1,2,3,4, and 5 on A1,B1,C1,D1, and E1, respectively.
I need a function that results in 20%,19%, 18%, 17%, and 17% on A2,B2,C2,D2, and E2.
However, if I have, like for example, 0,1,2,3,4,5 on A1,B1,C1,D1,E1, and F1 respectively, the results should be 20%,19%,18%17%, and 17% for B1,C1,D1,E1, and F1, neglecting A1 because it's zero. Meaning, I don't want to give any result when the value is 0.
Thank you
is it always 1,2,3,4,5
have you a few examples showing the expected results?
Last edited by martindwilson; 11-08-2013 at 10:28 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
1,2,3,4,5 are just hard coded numbers. It can either be any number as long as when the first non-zero number is typed/placed the 20%, 19%..... show up.
The expected results are the %s.
Last edited by managingcrap; 11-08-2013 at 10:40 PM.
i can only do it with a helper at the moment see attached
hi there. i'm thinking more of the lines of:
=IF(A1=0,"",LOOKUP(COUNTIF($A1:A1,"<>0"),{1,2,3,4,5},{0.2,0.19,0.18,0.17,0.17}))
so the 1st non zero number will be 20%? 2nd non zero 19% & so on?
Edit: actually just:
=IF(A1=0,"",LOOKUP(COUNTIF($A1:A1,"<>0"),{1,2,3,4},{0.2,0.19,0.18,0.17}))
Last edited by benishiryo; 11-08-2013 at 11:57 PM.
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Benishiryo,
u are right on "so the 1st non zero number will be 20%? 2nd non zero 19% & so on? " statement.
However, the result is incorrect when I put, for example, 2,20,1..etc. What I need is the first non-zero number should always result to 20%, the 2nd results to 19%, the 3rd to 18%, the 4th and succeeding numbers to 17%. Meaning, regardless if the numbers are 2,4,6,12, 15 or 5,2,3,16,56, the results should still be 20%,19%,18,17%.....
Thank you
Last edited by managingcrap; 11-09-2013 at 12:26 AM.
That's awesome. Thank you.
=IF(A1="","",LOOKUP(COLUMNS($A$1:A1)-MATCH(TRUE,INDEX(1:1<>"",0),0)+1,{1,2,3,4,5},{0.2,0.19,0.18,0.17,0.17}))
should work without helper
see attached
i cant get benishiryo 's to work see compare both formulas xlsx
ah do you actually have 0 or blank?
if its blank then
=IF(A1="","",LOOKUP(COLUMNS($A$1:A1)-COUNTIF($A1:A1,"="),{1,2,3,4},{0.2,0.19,0.18,0.17})) would work
Last edited by martindwilson; 11-09-2013 at 12:18 AM.
martindwilson,
I tried ur function and it worked.
Thanks guys. I just hope that one day I can be as good as you guys are with Excel stuff.
Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.
In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.
Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.
Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks