1

Sample Spreadsheet

I'm trying to simplify my spreadsheet

I have a spreadsheet that imports data from a table on a website. Sometimes this data is a percentage in decimal format, and sometimes the website doesn't have precise data, so it gives two numbers. For example, "7-13%". I use =SPLIT to separate these two numbers into Columns D & E, then in Column F, I convert them to decimal percent and average them. This percentage is then multiplied by the contents of Column B

Is there a simpler way to do this, and compress it down into one column instead of three?

3
=IF(ISNUMBER(SEARCH("–", C3)), (B3*sum(split(C3,"–%"))/2)*0.01, B3*C3)

Seems to be what I'm looking for.

1
=SUMPRODUCT(REGEXEXTRACT(C3,"(\d+)-(\d+)%"))/200*B3    

If data occasionally comes as single digits without "-", Try this:

=IFERROR(SUMPRODUCT(REGEXEXTRACT(C3,"(\d+)-(\d+)%"))/200*B3,C3*B3)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.