CODEWITHSUNDEEP
excelexcel-formulaconditional-statementsaggregatecell
Ryan Rosenthal
Ryan Rosenthal

Reputation: 1

In excel I want to write a formula that when a number is presented its translated to text

I want a formula to do aggregate score between columns a,b,and c and when the aggregate is determined I want this to show different text depending on the number. 10 = Effective 8-9.9 = Needs improvement 8.9 and below = action required.

I expect the formula to calculate a b and c and then notice the score and display the correct text.

the product should look as below although this is done manually the product should look as below although this is done manually

Upvotes: 0

Views: 109

Answers (3)

nkalvi
nkalvi

Reputation: 2614

With SWITCH - I think this is easy to adopt and scalable to some degree.

Microsoft 365

=LET(
    avg, BYROW(A2:C5, LAMBDA(r, AVERAGE(r))),
    SWITCH(
        TRUE,
        avg < 8, "Action Required",
        avg < 10, "Needs improvement",
        avg = 10, "Effective",
        "Check entries"
    )
)

Fill down

=SWITCH(
    TRUE,
    AVERAGE(A2:C2) < 8, "Action Required",
    AVERAGE(A2:C2) < 10, "Needs improvement",
    AVERAGE(A2:C2) = 10, "Effective",
    "Check entries"
)

Formula and result

Upvotes: 0

teylyn
teylyn

Reputation: 35970

This can be done with a simple lookup formula.

Let's assume the aggregate is an average across the three columns K, L, and M..

In order to be scalable, a solution with IFS() or CHOOSE() is not ideal.

  • dealing with decimals of the aggregate is more complex and can be inaccurate (Choose() cutting off instead of rounding), which makes a solution with IFS or Choose more convoluted that needed.
  • if your parameters change, you have to edit/replace every single formula

It's far easier to use a lookup function with a lookup table. That can be on a separate sheet or in another area of your data input sheet. Let's assume the lookup table is called "LookupTable" (using Ctrl + T to make the data into a table) and has the columns Score and Text.

Now you can use a simple Xlookup. No need for a convoluted construction.

=XLOOKUP(AVERAGE(K2:M2),LookupTable[Score],LookupTable[Text],"not found",-1)

enter image description here (for illustration purposes I have added a column that shows the result of the Average() function)

If you have an earlier Excel version that doesn't have Xlookup, you can use Vlookup. For that, you need to sort your Lookup table so the scores are in ascending order. Then you can use

=VLOOKUP(AVERAGE(K2:M2),LookupTable,2,1)

Should your parameters and grading change, you can easily just change the score thresholds in the lookup table. You can also easily insert rows in the lookup table if you need additional grading steps. And you won't have to touch either the XLookup or the VLookup formulas to make any adjustments.

Upvotes: 1

Black cat
Black cat

Reputation: 6271

If function IFS is available then this formula in cell D19 and drag down

=IFS(SUM(A19:C19)>10,"BAD INPUT",SUM(A19:C19)=10,"EFFECTIVE",SUM(A19:C19)>8,"NEED IMPROVEMENT",TRUE,"ACTION REQUIRED")

with LET function

=LET(rng,SUM(A19:C19),IFS(rng>10,"BAD INPUT",rng=10,"EFFECTIVE",rng>8,"NEED IMPROVEMENT",TRUE,"ACTION REQUIRED"))

gives the following:

formula result

If cannot use IFS consecutive IF can be used.

=IF(SUM(A19:C19)>10,"BAD INPUT",IF(SUM(A19:C19)=10,"EFFECTIVE",IF(SUM(A19:C19)>8,"NEED IMPROVEMENT",IF(TRUE,"ACTION REQUIRED"))))

Upvotes: 1

Related Questions