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

Fruit Calculation

K

keanur

Member
Hi Excel Ninjas,


I have a table of fruit selling. In this table there are certain categories for the customer to have different price for different booking window. Start from last minute order 1-6 days in advance will get 5% upto 90 days in advance will get 25% of Banana Master Price. I would like to know how I can apply this on excel:

  • what kind of formula that can refer the “date” to count the booking window
  • how can the formula detect if the date is keep changing day by day ie: today is 10 September and tomorrow is 11 September and so on
I made a simple formula, however i have no idea on how can i implement if it is more than 6 days. Please advise a simple formula and kindly brief me on the logic so i can learn and adapt it in the future. Thanking you in advance.

regards,
kean
 

Attachments

  • Calculation Book.xlsx
    10.9 KB · Views: 3
navic said:
Try in B13 copy across
Code: 
=B$12-(B$12*VLOOKUP(IF(B$11-$B$1<1;2;B$11-$B$1)+1;$C$2:$E$6;3))
Please see attached example
Click to expand...
Hi Navic,

Thank you for this, i can see that the formula is working. do you mind to explain further the formula, what is the function of every formula. Appreciate your feedback on this
 
To fully understand the formula, you need to know the functions and their arguments.
You can see the formula calculation step by step if you use 'Excel Evaluate Formula'.
You also need to know how to nest a function within another function. You can use the 'F9' key to do this.
My English is not good, but I will try to explain how the formula works.
I can't go into details, because I should write a lot. I'll shorten the explanation for you to understand.

The first formula, is based on a 'Variable Date' and a 'Today Date'. When you copy this formula to the right, it returns the results in a sequence ie numbers 0,1,2,3,4 ....
Code: 
B$11-$B$1
The next step is to nest the formula above, within the function 'IF', at the location of the argument "logical_test". I hope you know how the 'IF' function works so I will not explain its result. When you copy the 'IF' formula to the right it also returns the sequence numbers, 2,3,4,5,6,7, ...
I added 1 to the result of the 'IF' formula to eliminate the number on the border, eg 6/7.
Code: 
IF(B$11-$B$1<1;2;B$11-$B$1)+1
The next formula is the VLOOKUP function/formula. The 'IF' formula above, is nested inside the Vlookup function at the location of the argument "lookup_value" as criteria.
The lookup formula looks for a criterion in the range '$C$2:$E$6' and returns a percentage.
Notice that I left out the last argument in the Vlookup function (I could have set 'TRUE' for the last argument because it's the same if I omit it). I have done so that in the event that VLOOKUP does not find the exact number, taking into account the first smaller number from the 'C' column.
Of course, I set the number 3 to indicate the column from which Vlookup should return the result.
Code: 
VLOOKUP(IF(B$11-$B$1<1;2;B$11-$B$1)+1;$C$2:$E$6;3)
Finally, vlookup formula I is nested within the basic function to calculate the percentage of a certain value.
Code: 
B$12-(B$12*VLOOKUP_result)
I suppose this could have been solved in a simpler way, but at that moment I had no other idea.
I hope I explained it and you will understand. If not, maybe one of the colleagues in the forum explains more clearly than I do.
Please see attached explanation.
 

Attachments

  • keanur-navic42552-2.xlsx
    13.5 KB · Views: 5
My apologies
I can see now that as a delimiter instead of a comma (,) I use a semicolon (; ) in the posts above.
I use Non-US settings (Eastern Europe).
 
navic said:
To fully understand the formula, you need to know the functions and their arguments.
You can see the formula calculation step by step if you use 'Excel Evaluate Formula'.
You also need to know how to nest a function within another function. You can use the 'F9' key to do this.
My English is not good, but I will try to explain how the formula works.
I can't go into details, because I should write a lot. I'll shorten the explanation for you to understand.

The first formula, is based on a 'Variable Date' and a 'Today Date'. When you copy this formula to the right, it returns the results in a sequence ie numbers 0,1,2,3,4 ....
Code: 
B$11-$B$1
The next step is to nest the formula above, within the function 'IF', at the location of the argument "logical_test". I hope you know how the 'IF' function works so I will not explain its result. When you copy the 'IF' formula to the right it also returns the sequence numbers, 2,3,4,5,6,7, ...
I added 1 to the result of the 'IF' formula to eliminate the number on the border, eg 6/7.
Code: 
IF(B$11-$B$1<1;2;B$11-$B$1)+1
The next formula is the VLOOKUP function/formula. The 'IF' formula above, is nested inside the Vlookup function at the location of the argument "lookup_value" as criteria.
The lookup formula looks for a criterion in the range '$C$2:$E$6' and returns a percentage.
Notice that I left out the last argument in the Vlookup function (I could have set 'TRUE' for the last argument because it's the same if I omit it). I have done so that in the event that VLOOKUP does not find the exact number, taking into account the first smaller number from the 'C' column.
Of course, I set the number 3 to indicate the column from which Vlookup should return the result.
Code: 
VLOOKUP(IF(B$11-$B$1<1;2;B$11-$B$1)+1;$C$2:$E$6;3)
Finally, vlookup formula I is nested within the basic function to calculate the percentage of a certain value.
Code: 
B$12-(B$12*VLOOKUP_result)
I suppose this could have been solved in a simpler way, but at that moment I had no other idea.
I hope I explained it and you will understand. If not, maybe one of the colleagues in the forum explains more clearly than I do.
Please see attached explanation.
Click to expand...
Hi Navic,

Thank you so much to share your time and writing the whole explanation, very much appreciated it! i will try to understand the formula one by one and maybe i will come back and ask you further should i need some enlighten. Thank you once again.

cheers
 
You could use this one also
in cell B13
=B12-(B12*LOOKUP(IF(B11-$B$1<=0,1,B11-$B$1),--LEFT($D$2:$D$6,SEARCH(" ",$D$2:$D$6)),$E$2:$E$6))
 
if u have used - in separation of your days value then use - in search function instead of space (" ")
then use this one:
=B12-(B12*LOOKUP(IF(B11-$B$1<=0,1,B11-$B$1),--LEFT($D$2:$D$6,SEARCH("-",$D$2:$D$6)-1),$E$2:$E$6))
 
You must log in or register to reply here.
Back
Top