Calculate a series number

Dear Friends,
Need help how to create a formula
in column A there is 2/100,3/400, 5/100, 6/100, 7/100, 8/100 and in column B is total. total should be 900
Please advise which formula need. for details, please find the attached file. thank you

Wie

You might want to view the attachment, Hitesh.

Originally Posted by AliGW

You might want to view the attachment, Hitesh.

When the question was asked, there was no attachment

Hi

Although a formula could work for your sample,

I doubt that a formula is what you are looking for.

Paste this code into a Macro Module.

Please Login or Register  to view this content.

Then put this formula into B2.

Formula:

Please Login or Register  to view this content.

If I may say, the attachment does not include too many additional details. Are there more requirements or limitations or scenarios? Are you required to perform the entire summation in a single cell formula, or are you open to other possibilities? What exactly is the logic behind the 900 results? As near as I can tell, it is simply the sum of the denominators/qty values (100+400+100+100+100+100), is that correct, or is there more to it?

Here's how I would probably approach something like this:

1) Text to columns to split the text string out and isolate the individual values into individual cells. Use delimited by comma "," and slash "/".
2) Then you can simply sum every other column, or use a SUMIF()/SUMIFS() function if you have a header row with size/qty.

Is that an approach you would be interested in?

Originally Posted by FDibbins

When the question was asked, there was no attachment

Yes, I know, which is why I pointed out that there now was one. I also removed the attachment from post #1 as it is repeated in post #4.

Quick tutorial on using the Text to Columns command: http://www.excel-easy.com/examples/text-to-columns.html
Help file for the SUMIFS() function: https://support.office.com/en-us/art...6-611cebce642b

Put "size" into A1, "qty" in B1, then copy those two cells across as far as needed.
With delimited text in A2 (and down, if there is more than one row of data), execute text to columns.
In column to the right of parsed data, enter a SUMIFS() function. Maybe in AA2 =SUMIFS(A2:Z2,A$1:Z$1,"qty") Note the mix of relative and absolute references for easy copying down to additional rows.

Here's another possibility. In B2, copied across until 0s are returned:

=IFERROR(MID(TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",100)),100*(COLUMNS($A:A)-1)+1,100)),FIND("/",TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",100)),100*(COLUMNS($A:A)-1)+1,100)))+1,255)+0,0)

Then just add them up!

This should give you the total of 900
Enter as regular formula

Formula:

Please Login or Register  to view this content.

The part {1,2,3,4,5,6} count of slashes in the string.

Alkey... Only just brilliant. Truly the indisputable King of parsing!!!!

I have modified the above formula to make the count of slashes dynamic.

Formula:

Please Login or Register  to view this content.

Sorry, didn't see the attachment.

With the values in A1:A6 enter this Array Formula in B1 (enter with Ctrl+Shift+Enter)

Formula:

Please Login or Register  to view this content.

	A	B
1	2/100	900
2	3/400
3	5/100
4	6/100
5	7/100
6	8/100

or can try below formula

Please Login or Register  to view this content.

Hi Alkey

Thanks for the lesson

But if you change A2 to 2/100,3/400,5/100,6/100,7/2, 8/7 your formula returns 715.

The only way I could get it to return 709 was modify it to:-

Entered using Ctrl Shift Enter:

Formula:

Please Login or Register  to view this content.

Please show me how it should be done.

Thankyou, Phuocam,

That is much nicer.

deleted wrong thread