• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup a rate * %, based on two different award/groups

Hello, I hope you are travelling ok with the current global lockdown

I have figured out how to do the multiplication I required based on one Award Level i.e. LSA

The value in C5 should be based on the

Award & Level selected in C3 x

(Select the Age in E7, I need this to apply the relevant % from the table in K2:M15) x

Multiplied by the Adult rate in C12:C16

The correct answers are between column E & I based on the Award Level & age selected

I find it so hard to articulate what I want to do, which I feel is just as bad at trying to figure out the calculation.

Any questions, please don't hesitate to ask.

thank you, as always forever in your debt.

Kelli
 

Attachments

  • Chandoo IF or Statement V1.xlsx
    12.1 KB · Views: 5
Hi P45cal,

I've been working with your formula and it has been working great.

Can I push the envelope even further by adding one more condition i.e. if Casual

In the attached in C9 I've added a drop down box to select Casual, IF Casual it is the same result multiplied by 1.25 i.e. plus 25% (everyone else is 100%)

thank you,
 

Attachments

  • Chandoo IF or Statement V1.xlsx
    12.8 KB · Views: 5
Hi Kelli,

Maybe like this, adding the blue part to the existing formula.
=IF($E$9="Casual";1,25;1)*SUM(VLOOKUP($E$7;$L$6:$M$14;2)*VLOOKUP($C$3;$B$13:$C$17;2;0)*38)*52
or
=(1+($E$9="Casual")*0.25)*SUM(VLOOKUP($E$7;$L$6:$M$14;2)*VLOOKUP($C$3;$B$13:$C$17;2;0)*38)*52
 

Attachments

  • Copy of Chandoo IF or Statement V1.xlsx
    12.9 KB · Views: 3
Just for the sake of being different, I used a two-way XLOOKUP.
Code:
= ( XLOOKUP(awardLevel, awardLevelHdr, rate)
    XLOOKUP(age, ageHdr, rate) )
  * IF( age="adult", 52, 1 )
  * IF( status="Casual", 1.25, 1 )
The first lookup returns a row from the rate data table, whilst the second returns the column appropriate to the age. Since both are range references, they may be intersected to give the required rate.
The multiplicative factors allow for casual employment and the fact that the adult rate is quoted weekly rather than annual.

By the way, the rates I inserted for the shaded out areas do not agree with the values returned by the existing formulae.
 

Attachments

  • Two way lookup (PB).xlsx
    14.2 KB · Views: 2
Back
Top