• 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.

Need Formula

S

SUHAS KARHADKAR

Member
Respected All,

Attached herewith an Excel File. I need formula.

File Name - Units.xls

Regards.

Suhas
 

Attachments

  • Units.xlsx
    10.5 KB · Views: 13
Hi @SUHAS KARHADKAR

This should work, although I'm sure it can be simplified
In line 6 I entered the units you would usually type in each cell... line 7 has the accumulated value and acts as a intermediate step for the formulas (easier to read this way)
 

Attachments

  • Units.xlsx
    11.7 KB · Views: 13
Last edited:
Thanx very much PCosta Sir for reply.
You take another Cell for 500, 1500, 2000, 6000 etc. and also take another for rate i.e. 530. Is there any way to extract the value without taking different cell? Means I want to extract 500 from 6000 by giving formula in same cell without inserting any row, column or cell? Is it possible?

Again thank you very much.

Regards

Suhas
 

Attachments

  • Units.xlsx
    11.7 KB · Views: 5
SUHAS KARHADKAR said:
Thanx very much PCosta Sir for reply.
You take another Cell for 500, 1500, 2000, 6000 etc. and also take another for rate i.e. 530. Is there any way to extract the value without taking different cell? Means I want to extract 500 from 6000 by giving formula in same cell without inserting any row, column or cell? Is it possible?

Again thank you very much.

Regards

Suhas
Click to expand...
Hi,

Yes, you can indeed extract the values from the string of text in the already available cells... in this case you can take Rate Rs. from "E1" (530) and the units & Valuation Rate from "F4:I4" by using a combination of mid, search and len functions (there are other ways, this is just one of them)

I've done so in the attachment but the resulting formula is far from elegant and extremely hard to read... mainly because of the accumulated values which, by not having an intermediate step, require you to sum the values extracted from the cells (which means repeating the formulas for those a bunch of times).

In any case, it should be working... just make sure that when changing the values we want to extract from the cells you do not change anything else since the search functions is looking for specific strings of text in order to return the values.

Let me know if that was of any help.
 

Attachments

  • Units.xlsx
    12.6 KB · Views: 10
Thank you very much PCosta Sir.
I haveo gone through. calculations are correct, but it seems not feasible because I have lots of sheets to work on. Is there any simplified solution?

Thanking you.

Suhas
 
SUHAS KARHADKAR said:
Thank you very much PCosta Sir.
I haveo gone through. calculations are correct, but it seems not feasible because I have lots of sheets to work on. Is there any simplified solution?

Thanking you.

Suhas
Click to expand...
Hi

I don't think any solution involving retrieving values from strings of text will be much simpler. The first solution, or, even better, @Mamdouh Elfors solution, is probably the best way to go about doing that, but since you need to extract the values from the available cells, without adding anything, at the moment, nothing else comes to mind.
 
You must log in or register to reply here.
Back
Top