simplyfuing if formula

[dhineshreddy]

Hello Team,

I am looking for a simplified formula using IF.

I have attached the excel where I would need to calculate the points using source calculation mentioned in source calculation table.
I'm writing a formula but it is getting snaky long.

your help would be appreciated.

Many thanks!
D.

[Jonmo1]

In D2 and filled down, try

=SUMIFS($R$14:$R$22,$P$14:$P$22,B2,$Q$14:$Q$22,C2)

[dhineshreddy]

Thank you Sir,

Source calculation is only for reference.
unfortunately I can not use source cells in the formula.

I will need to use IF chain as I will need to put it in the middle of the other formula I have.

Many thanks!

BR,
D.

[63falcondude]

Here's one option:

=IF(OR(B2&C2={"not okn/a","n/anot ok"}),0,SUM(LOOKUP(B2:C2,{"n/a","not ok","ok"},{0.5,0,0.5}))) Ctrl Shift Enter

[Jonmo1]

unfortunately I can not use source cells in the formula.

Why Not?

It would be very advantageous to use the table written in a range of cells.
It's much easier to maintain that table, than a hard coded formula.

You can put it on another sheet, and hide that sheet.

[dhineshreddy]

Hello my friend, it does not give desired output. it gives 1 point for all the employees which is incorrect.

thanks for your time!

[dhineshreddy]

I get your point.... have used that way before...

But in this case, I am not allowed to change the structure of the Excel....:-)...
may sound weird... but it is what it is.

[63falcondude]

I beg to differ. See attached workbook.

The values produced from the formulas in column D match the expected output from column R exactly.

[Jonmo1]

I am not allowed to change the structure of the Excel

That's when I say to the boss, 'you came to me for help with this task, this is the best method in my expert opinion'

Anyway, if you must. Here's a brute force method.
Assuming the 2 cells both have only the 3 possible values.

=IF(B2="ok",IF(C2="not ok",0.5,1),IF(B2="n/a",IF(C2="not ok",0,1),IF(C2="ok",0.5,0)))

[dhineshreddy]

Appologies, It has to be something wrong with my Excel... if I say CNTRL D, it only copies the top number... thank you so much..

[Jonmo1]

Calculation is set to manual (on the formulas tab) in the book you shared.

[63falcondude]

You're welcome. After seeing Jonmo1's solution, I would use that one over mine.

If that solved your question, please mark this thread as SOLVED.

[dhineshreddy]

Many thanks my friend.... very kind of you..... sums up my day quite nicely...
I have never been disappointed by this forum. kudos....

[63falcondude]

Thank you for the rep!