Forum Discussion
How to find and calculate cells which make up given amount?
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?
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?
Hello,
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.
Thanks,
Hi Asad,
To add the formula we need to have some assumption what are dependencies 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:
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.
