• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to sum

Frankly speaking Sanjeev - I really cant understand your requirement...appreciate if you could explain in detail..
 
Hi Sanjeev, I think I understand your requirement, that you are "gathering" data from other cells, then carrying out the "math", but can't you just not simply use the cell ref/absolute refs to do this?, for example;

Your example: 45+25+78-78+25

A1 _ 45
A2 _ 25
A3 _ 78
A4 _ 78
A5 _ 25

So: A1+A2+A3-A4+A5


regards
Steve
 
Thats great, but be carefull... The current formula will give you an output of 95, but if you change the value @A4 to a negative value (i.e -75) then your output would be different.... :confused:
 
Hi Sanjeev,
Try:
=MID(F6,1,2)+0+MID(F6,4,2)+0+MID(F6,7,2)+0+MID(F6,10,2)+0+MID(F6,13,2)+0

Not sure i've fully understood your requirement.

Regards,
 
its not dynamic when i input 3 or 4 digit or add more no its not working

it should be working when i use + - * /
 
Excel has an Evaluate function that does what you want, but that function has 2 problems.

First, is that it is actually a Microsoft Excel 4.0 Macro, so you have to enable macros on your file.
Second, is that it can only be used in the definition of a Name, if you try to use the formula directly in a cell you'll get an error.
 

Attachments

  • Book3.xlsm
    8.8 KB · Views: 11
Hi Sanjeev,

Try this with Ctrl+Shift+Enter :

=SUM(IFERROR(TRIM(MID(SUBSTITUTE(SUBSTITUTE(F6,"+",REPT(" ",99)),"-",REPT(" ",99)&"-"),ROW(A1:A100)*99-98,98))+0,0))

Hope that helps.


Edit: I just read requirement of * and / so this will not work there.
 
Last edited:
Haz said:
Excel has an Evaluate function that does what you want, but that function has 2 problems.

First, is that it is actually a Microsoft Excel 4.0 Macro, so you have to enable macros on your file.
Second, is that it can only be used in the definition of a Name, if you try to use the formula directly in a cell you'll get an error.
Click to expand...
Hi Haz,

very neat technique... the only limitation is you cannot send cell reference as parameter/arguments. Ex Result is hard coded to F6, and if I need to do similar action for F6 to F10 etc then i Need to define Result1, Result2 etc...

Do you think there is any improvement for this Evaluate function? Pls provide more insights about this Microsoft Excel 4.0 Macro?

Regards,
Prasad DN
 
You must log in or register to reply here.
Back
Top