How to find and calculate cells which make up given amount?

Question

Hello,I am using excel and forgot the formula which I can employee to find the cells and calculate them so i get my desired amount. I have the total amount already but don't know which cells are equal to that amount. Kindly some body tell me how can I find the cells in a given range which are equal to amount I have.&nbsp;&nbsp;Thanks,

sergeibaklan · Answer

Hi Asad,&nbsp;If simply find the cell you may filter your range or use Find from ribbon menu with Look in Values options. Or you mean something else?

asad ur rehman · Answer

I know the filter method but I want formula.

I have a large range of values and my desired value is 15000. Now I want ot identify the cells and calculate them so they make up this 15000, I already have?

sergeibaklan · Answer

When i didn't catch. You'd like to find the cell with some value (INDEX/MATCH, VLOOKUP, etc.) and after that make some calculations with result; or you'd like to change some cells to make the result as targeted (Solver, etc.); or what?

What exactly "identify the cells" means?

asad ur rehman · Answer

Hello,&nbsp;I am sharing one snapshot of my data I have. I have a list of values and one target less that total sum. I don't know the vlaues which make up target. I want to find those cells which make up target value. I need the formula which I put in formula bar and get result.&nbsp;Thanks,

sergeibaklan · Answer

Hi Asad,&nbsp;To add the formula we need to have some assumption what are dependencies&nbsp;for the values related to different type of vehicles (or/and day of week, or something else). For example, that could be the assumption what if maximum daily rate is 1 when for vehicle type "a" it'll be 0.25, etc. You may add helper column to your range with such proportion (or even better to keep it in separate table) and when calculate actual rate depends on target:&nbsp;That could be other assumption or constrains (when use Solver), but we shall to have one. Otherwise we may, for example, simply divide the target on number of records to have the result.

Forum Discussion

How to find and calculate cells which make up given amount?

5 Replies

Resources