CODEWITHSUNDEEP
excelexcel-formula
mohd aizat
mohd aizat

Reputation: 15

Excel Shows Multiple Value

The value from each cells Range I12:AM12. Cell Value

=SUM(IF(IF(ISNUMBER(FIND($BE$10,I12:AM12)),VALUE(LEFT(I12:AM12,FIND($BE$10,I12:AM12)-1)),0)>=8,IF(ISNUMBER(FIND($BE$10,I12:AM12)),VALUE(LEFT(I12:AM12,FIND($BE$10,I12:AM12)-1)),0),0))-(BE12*8)

This excel formula should show output of 6.0 as per below.

Output

However, when trying to change the formula, or edit. The value will change to -24.0. Even not edit it yet, just press enter on the formula itself will make it return different value than what it supposed.

Unwanted Output

Need your guys help with this.

I've tried various solutions, generate new formula, even using . The problem is still unsolved.

The formula supposed to find value with "P" in the range cells which is more or equal to 8 and minus with 8. Then, do the addition of the total sum after minus.

In this case the value shoud retun 6.0.

Example: Cells Range I12:AM12 contains various value some of it have contains letter "P" and the cell value that contains "P" is the only value that needed to be count, apart from other values. The "P" letters is use to flag the value only.

The formula seems to be correct, however, after I'm about to change the value from 8 to 7.5 the output will be change to -24.0 instead of correct value. And, I will never get the output of 6.0 again, even if I change it back to 8.

Upvotes: 1

Views: 69

Answers (1)

Dattel Klauber
Dattel Klauber

Reputation: 833

This formula will do the trick:

=LET(
    data,I12:AM12,
    subset,FILTER(data,NOT(ISERROR(FIND("P",data)))),
    num,VALUE(LEFT(subset,LEN(subset)-2)),
    SUM(num-8)
)

This solution requires the Office365 version of Excel.

Note, with the expression LEN(subset)-2 within the formula, it assumes that the number is always seperated from the "P" by one character, e.g. a space. That means, the formula only works this way if values featuring a "P" appear in the form "5 P" or "11 P", but not in the form "11P".

For earlier versions of excel where the functions LET() and FILTER() are not available, the formula here below can be used instead

=SUM(IFERROR(FIND("P",I12:AM12)^0*VALUE(LEFT(I12:AM12,2))-8,0))

This is still an array formula and has to be entered with the key combination CTRL+SHIFT+ENTER

Upvotes: 1

Related Questions