Increment %
- Thread starter Lasantha
- Start date
Naresh Yadav
Active Member
Dear Lasantha,
I hope attached sheet will help you
regards
Naresh
I hope attached sheet will help you
regards
Naresh
rahulshewale1
Active Member
thank you
thank you
thank you
Peter Bartholomew
Well-Known Member
I liked Bosco's cunning use of IMAGINARY so, building on that with modern Excel, I used the lower threshold to each band to give
= XLOOKUP(appraisalScore, IMREAL(appraisalBand&"j"), Increment, , -1 )
or, even more aggressively up to date
= LET(
threshold, IMREAL(appraisalBand&"j"),
empIncrement, XLOOKUP(appraisalScore, threshold, Increment, , -1 ),
empIncrement )
One might argue to case for defining the threshold values using
= VALUE( LEFT( appraisalBand, SEARCH("-", appraisalBand) - 1 ) )
despite it being less innovative.
Note: These formulae are intended for Office 365 and the LET function requires insider channel.
= XLOOKUP(appraisalScore, IMREAL(appraisalBand&"j"), Increment, , -1 )
or, even more aggressively up to date
= LET(
threshold, IMREAL(appraisalBand&"j"),
empIncrement, XLOOKUP(appraisalScore, threshold, Increment, , -1 ),
empIncrement )
One might argue to case for defining the threshold values using
= VALUE( LEFT( appraisalBand, SEARCH("-", appraisalBand) - 1 ) )
despite it being less innovative.
Note: These formulae are intended for Office 365 and the LET function requires insider channel.
deciog
Active Member
bosco_yip and
Peter Bartholomew
These functions will help a lot in my project
Thank you so much for sharing your experiences
Decio
Peter Bartholomew
These functions will help a lot in my project
Thank you so much for sharing your experiences
Decio
Last edited:
shrivallabha
Excel Ninja
If you create a source table like
Then old warhorse VLOOKUP works just fine where $K$3:$L$7 houses above source table!
=VLOOKUP(B2,$K$3:$L$7,2,TRUE)
0 | 6% |
56 | 8% |
66 | 10% |
81 | 10% |
91 | 11% |
Then old warhorse VLOOKUP works just fine where $K$3:$L$7 houses above source table!
=VLOOKUP(B2,$K$3:$L$7,2,TRUE)
Peter Bartholomew
Well-Known Member
@deciog
The LET function is something new, currently on beta release within Office 365 insider channel. It came as a bit of a shock to me earlier in the year.
It takes an alternating pattern of parameters with the first of any pair being a name that is local to the LET function and the second being its defining formula. The names defined this way can be used in subsequent formulas within the LET but not elsewhere. Unlike normal named formulas, these names are evaluated at the point at which they are defined and so may be used multiple times in the function without being recalculated.
I have used Alt+Enter to display the arguments over multiple lines simply to make the formula more readable.
The short answer to "Where are these items?" is "right in front of you"; you are looking at their definition.
I hope that helps (assuming it translates into Portuguese OK in the first place) .
Cheers
Peter
The LET function is something new, currently on beta release within Office 365 insider channel. It came as a bit of a shock to me earlier in the year.
It takes an alternating pattern of parameters with the first of any pair being a name that is local to the LET function and the second being its defining formula. The names defined this way can be used in subsequent formulas within the LET but not elsewhere. Unlike normal named formulas, these names are evaluated at the point at which they are defined and so may be used multiple times in the function without being recalculated.
I have used Alt+Enter to display the arguments over multiple lines simply to make the formula more readable.
The short answer to "Where are these items?" is "right in front of you"; you are looking at their definition.
I hope that helps (assuming it translates into Portuguese OK in the first place) .
Cheers
Peter
This is Great .
thank you.