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

Formula to Count from a table based on maximum variable number

R

rajkenya1

Member
Hi Guys

I need some assistance on a formula.

In the attached spreadsheet i have a table which has years in column B, amount of people in column C and total amount in column D. In cell C25 i have a variable total number of people. I want a formula in column F that will bring the resultant number up to when the total reaches the number in cell C25. Remember Cell C25 is variable. The balance of the number of people will appear in the following year till its maxed out to that variable number in C25. Similarly the same with the corresponding total amount. The number needs to be rounded off to get the exact number as per Cell C25.

For example in the current table the number in cell C25 is 36. So the formula in cell F5 should look up the number and if its less that Cell C25 then put it in cell F5 and so on till it reaches say cell F19 where it will need to be rounded off to get the exact number.

Lol...hope what i have said makes sense to someone who can assist me.

Thanks
 

Attachments

Hi, Raj,

First can you clarify the purpose of formulas in column B & C, as they are returning a circular ref. error.

Regards,
 
Hi Somedra. Thanks for your reply. Ok ignore the formuales in B and C. Paste those numbers as hard values and then try and see if you can help me with my request. I have actually uploaded the file again.
 

Attachments

Dear Swapnil

Thanking you for your reply. I remember you helped me with a similar one as well. Thanks Very much again. Its what i needed absolutely. Cheers
 
Dear Somendra

Thanking you also for your never ending help. Your solution is slightly different from Swapnil about but still works well. Thank you again Somendra. By the way what about 2016? Is it the same formula i can use as per what you have in 2015.

Thanks so much. Its amazing how you super users all have a different way of coming up with solutions.
 
Last edited:
Hi Somendra

Thanks again for your reply. Its a carried forward balance. I think Swapnil has resolved it. Have a look at his file earlier in the thread. Its a different formula and it seems to have worked.
 
Somendra Misra said:
See the file.

Regards,
Click to expand...

Just have one concern , In Result amount column , formula should be =IFERROR(IF(C5=F5,D5,D5*F5/C5),0) , This will give you proportional cost.

In your formula , If I look at Row 19 , Cost for 3 People is less than what is there for 1 people.

Please correct me If my understanding is wrong.
 
Hi Swapnil and Somendra

Just a further query based on yesterdays spreadsheet help. See attached spreadsheet.

Basically if i have variable numbers in more than one cell for eg last query had a number only in cell C25, but now what if i have a variable number in Cell D25 and E25 as well and what i now need is if the number in Cell C25 is 1 then in 2015 it will only take 1 from the summary table and rest of the years 0. Similarly in 2016 if the number is Cell 25 is 4 then it look at the the summary table and take the next set of numbers after the ones in 2015 upto a total of 4. Similar in 2017 it looks at the cell E25 and the takes 5 after 2016. Is this possible.
 

Attachments

You must log in or register to reply here.
Back
Top