Is there a way to add a formula to unique data points in excel?

user_16458578 0
Viewed 57

I am trying to clean some data with this format:

A1 B1 C1 "0,1,0,E,1" "0,0,0,E" "0,1,1,1,2,E"

Raw data

To obtain the information, I will need to sum up each cell individually, and then calculate the sum over a row.

So far, I have replaced all of the "E"s (for empty) with no data, which removed the E's, and I have replaced all of the commas with "+" signs to add the numbers in the cell. To run the formula of each cell, I will need to now place an "=" in front of each text string, however, if I copy it in with something like "="="&A1" the formula will not run because excel is reading the = as a letter or symbol and not an operator. Do you know of a way to fix this problem?

Thank you so much!

data-cleaning validation excel
4 Answers

I think this will work for your version ...

=SUM(FILTERXML("<d><c>" & SUBSTITUTE(A1,",","</c><c>") & "</c></d>","//c"))

That's applied like below ...

Formula

... there's always someone smarter than me but that seems to work.

It’s not an in place replacement but it keeps your source data intact and provides a nice reconciliation point.

user_57720950

Using a separate sheet for each column of values, given data in cell A1 of

0,0,0,1,0,1,1,E,1,0

In C1, enter the formula

=IFERROR(MID(SUBSTITUTE(SUBSTITUTE($E$14,",",""),"E",""),COLUMN()-2,1)*1, "")

and drag the fill handle to to the right for as many cells as the longest number of data points you have.

Enter your SUM()formula in B1 (you can use the shortcut ALT + =).

user_12774020

Have you tried something like this, please refer the image, where its showing as per your required output, three alternative formulas

1.) Formula used in cell B2

=SUMPRODUCT(IFERROR(--MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1),0))

2.) Formula used in cell D2

=SUMPRODUCT(IFERROR(--MID($C2,SEQUENCE(LEN($C2)),1),0))

The 2nd formula is applicable to Excel 2021 & O365 Users only

3.) Formula used in cell F2

=SUM(IFERROR(--MID($E2,ROW($1:$1000),1),0))

This is an array formula, so requires to press CTRL SHIFT ENTER !

Three Alternative Formulas

user_81625200

Here is an update, the last formulas, which i have shared, shall work only when the digits are less than 10, however the present formula, shall work for all number of digits,

Formula used in cell B17

=SUMPRODUCT(IFERROR(--MID(SUBSTITUTE($A17,",",REPT(" ",100)),COLUMN(A1:Z1)*99-98,100),0))

Please adjust your range accordingly as per your data.

FORMULA TO ADD UNIQUE DATA POINTS

user_81625200
Related