|
Excel Jululian
Hi
I need your help on this formula I need to calculate Storage Fees Column (A) Date In Column (B) Date out Column (C) numbers of days in Stock (A1-B1)=112days The First ten days free The fees on First 20 days after the 10days = 1 dollar The fees on second 20 days after the 10days and 20 day = 2 dollar The fees on Third 20 days after the 10days,20day and 20day = 3 dollar The fees on remaining days after the 10days, 20day, 20day, and 20 day = 10 dollar Date In Date out Numbers of days 05/11/2008 25/02/2009 112 Regards |
Excel Jululian
=MAX(0,C2-10)*1+MAX(0,C2-30)*2+MAX(0,C2-50)*3+MAX(0,C2-70)*10
Regards, Stefi €˛George A. Jululian€¯ ezt Ć*rta: Hi I need your help on this formula I need to calculate Storage Fees Column (A) Date In Column (B) Date out Column (C) numbers of days in Stock (A1-B1)=112days The First ten days free The fees on First 20 days after the 10days = 1 dollar The fees on second 20 days after the 10days and 20 day = 2 dollar The fees on Third 20 days after the 10days,20day and 20day = 3 dollar The fees on remaining days after the 10days, 20day, 20day, and 20 day = 10 dollar Date In Date out Numbers of days 05/11/2008 25/02/2009 112 Regards |
Excel Jululian
Hi
I made the calulation and result is 540 and not as formaul 872 10 X 0 = 0 20 X 1 = 20 20 X 2 = 40 20 X 3 = 60 42 X 10 = 420 Total days 112 Total amount 540 "Stefi" wrote: =MAX(0,C2-10)*1+MAX(0,C2-30)*2+MAX(0,C2-50)*3+MAX(0,C2-70)*10 Regards, Stefi €˛George A. Jululian€¯ ezt Ć*rta: Hi I need your help on this formula I need to calculate Storage Fees Column (A) Date In Column (B) Date out Column (C) numbers of days in Stock (A1-B1)=112days The First ten days free The fees on First 20 days after the 10days = 1 dollar The fees on second 20 days after the 10days and 20 day = 2 dollar The fees on Third 20 days after the 10days,20day and 20day = 3 dollar The fees on remaining days after the 10days, 20day, 20day, and 20 day = 10 dollar Date In Date out Numbers of days 05/11/2008 25/02/2009 112 Regards |
Excel Jululian
Sorry, I didn't test it carefully. This is the right version:
=MAX(0,MIN(20,$C$2-10))+MAX(0,MIN(20,$C$2-30))*2+MAX(0,MIN(20,$C$2-50))*3+MAX(0,C2-70)*10 Regards, Stefi €˛George A. Jululian€¯ ezt Ć*rta: Hi I made the calulation and result is 540 and not as formaul 872 10 X 0 = 0 20 X 1 = 20 20 X 2 = 40 20 X 3 = 60 42 X 10 = 420 Total days 112 Total amount 540 "Stefi" wrote: =MAX(0,C2-10)*1+MAX(0,C2-30)*2+MAX(0,C2-50)*3+MAX(0,C2-70)*10 Regards, Stefi €˛George A. Jululian€¯ ezt Ć*rta: Hi I need your help on this formula I need to calculate Storage Fees Column (A) Date In Column (B) Date out Column (C) numbers of days in Stock (A1-B1)=112days The First ten days free The fees on First 20 days after the 10days = 1 dollar The fees on second 20 days after the 10days and 20 day = 2 dollar The fees on Third 20 days after the 10days,20day and 20day = 3 dollar The fees on remaining days after the 10days, 20day, 20day, and 20 day = 10 dollar Date In Date out Numbers of days 05/11/2008 25/02/2009 112 Regards |
Excel Jululian
Yes, I think Stefi missed a bit out of her formula, so she's not limiting
the 1 dollar range to 20 day, but continuing it in parallel with the 2 dollar range, and the higher ranges. You could change the =MAX(0,C2-10)*1+MAX(0,C2-30)*2+MAX(0,C2-50)*3+MAX(0,C2-70)*10 to =MAX(0,C2-10)*1+MAX(0,C2-30)*(2-1)+MAX(0,C2-50)*(3-2)+MAX(0,C2-70)*(10-3) or =MAX(0,C2-10)*1+MAX(0,C2-30)*1+MAX(0,C2-50)*1+MAX(0,C2-70)*7 or =MAX(0,C2-10)+MAX(0,C2-30)+MAX(0,C2-50)+MAX(0,C2-70)*7 Another way of changing's Stefi's formula would be =MIN(20,MAX(0,C2-10))*1+MIN(20,MAX(0,C2-30))*2+MIN(20,MAX(0,C2-50))*3+MAX(0,C2-70)*10 or =MIN(20,MAX(0,C2-10))+MIN(20,MAX(0,C2-30))*2+MIN(20,MAX(0,C2-50))*3+MAX(0,C2-70)*10 -- David Biddulph "George A. Jululian" wrote in message ... Hi I made the calulation and result is 540 and not as formaul 872 10 X 0 = 0 20 X 1 = 20 20 X 2 = 40 20 X 3 = 60 42 X 10 = 420 Total days 112 Total amount 540 "Stefi" wrote: =MAX(0,C2-10)*1+MAX(0,C2-30)*2+MAX(0,C2-50)*3+MAX(0,C2-70)*10 Regards, Stefi "George A. Jululian" ezt ķrta: Hi I need your help on this formula I need to calculate Storage Fees Column (A) Date In Column (B) Date out Column (C) numbers of days in Stock (A1-B1)=112days The First ten days free The fees on First 20 days after the 10days = 1 dollar The fees on second 20 days after the 10days and 20 day = 2 dollar The fees on Third 20 days after the 10days,20day and 20day = 3 dollar The fees on remaining days after the 10days, 20day, 20day, and 20 day = 10 dollar Date In Date out Numbers of days 05/11/2008 25/02/2009 112 Regards |
Excel Jululian
Many Thanks on your great support "David Biddulph" wrote: Yes, I think Stefi missed a bit out of her formula, so she's not limiting the 1 dollar range to 20 day, but continuing it in parallel with the 2 dollar range, and the higher ranges. You could change the =MAX(0,C2-10)*1+MAX(0,C2-30)*2+MAX(0,C2-50)*3+MAX(0,C2-70)*10 to =MAX(0,C2-10)*1+MAX(0,C2-30)*(2-1)+MAX(0,C2-50)*(3-2)+MAX(0,C2-70)*(10-3) or =MAX(0,C2-10)*1+MAX(0,C2-30)*1+MAX(0,C2-50)*1+MAX(0,C2-70)*7 or =MAX(0,C2-10)+MAX(0,C2-30)+MAX(0,C2-50)+MAX(0,C2-70)*7 Another way of changing's Stefi's formula would be =MIN(20,MAX(0,C2-10))*1+MIN(20,MAX(0,C2-30))*2+MIN(20,MAX(0,C2-50))*3+MAX(0,C2-70)*10 or =MIN(20,MAX(0,C2-10))+MIN(20,MAX(0,C2-30))*2+MIN(20,MAX(0,C2-50))*3+MAX(0,C2-70)*10 -- David Biddulph "George A. Jululian" wrote in message ... Hi I made the calulation and result is 540 and not as formaul 872 10 X 0 = 0 20 X 1 = 20 20 X 2 = 40 20 X 3 = 60 42 X 10 = 420 Total days 112 Total amount 540 "Stefi" wrote: =MAX(0,C2-10)*1+MAX(0,C2-30)*2+MAX(0,C2-50)*3+MAX(0,C2-70)*10 Regards, Stefi "George A. Jululian" ezt Ć*rta: Hi I need your help on this formula I need to calculate Storage Fees Column (A) Date In Column (B) Date out Column (C) numbers of days in Stock (A1-B1)=112days The First ten days free The fees on First 20 days after the 10days = 1 dollar The fees on second 20 days after the 10days and 20 day = 2 dollar The fees on Third 20 days after the 10days,20day and 20day = 3 dollar The fees on remaining days after the 10days, 20day, 20day, and 20 day = 10 dollar Date In Date out Numbers of days 05/11/2008 25/02/2009 112 Regards |
Excel Jululian
Many Thanks on your great support
"Stefi" wrote: Sorry, I didn't test it carefully. This is the right version: =MAX(0,MIN(20,$C$2-10))+MAX(0,MIN(20,$C$2-30))*2+MAX(0,MIN(20,$C$2-50))*3+MAX(0,C2-70)*10 Regards, Stefi €˛George A. Jululian€¯ ezt Ć*rta: Hi I made the calulation and result is 540 and not as formaul 872 10 X 0 = 0 20 X 1 = 20 20 X 2 = 40 20 X 3 = 60 42 X 10 = 420 Total days 112 Total amount 540 "Stefi" wrote: =MAX(0,C2-10)*1+MAX(0,C2-30)*2+MAX(0,C2-50)*3+MAX(0,C2-70)*10 Regards, Stefi €˛George A. Jululian€¯ ezt Ć*rta: Hi I need your help on this formula I need to calculate Storage Fees Column (A) Date In Column (B) Date out Column (C) numbers of days in Stock (A1-B1)=112days The First ten days free The fees on First 20 days after the 10days = 1 dollar The fees on second 20 days after the 10days and 20 day = 2 dollar The fees on Third 20 days after the 10days,20day and 20day = 3 dollar The fees on remaining days after the 10days, 20day, 20day, and 20 day = 10 dollar Date In Date out Numbers of days 05/11/2008 25/02/2009 112 Regards |
Excel Jululian
Many thanks on great support
"Stefi" wrote: Sorry, I didn't test it carefully. This is the right version: =MAX(0,MIN(20,$C$2-10))+MAX(0,MIN(20,$C$2-30))*2+MAX(0,MIN(20,$C$2-50))*3+MAX(0,C2-70)*10 Regards, Stefi €˛George A. Jululian€¯ ezt Ć*rta: Hi I made the calulation and result is 540 and not as formaul 872 10 X 0 = 0 20 X 1 = 20 20 X 2 = 40 20 X 3 = 60 42 X 10 = 420 Total days 112 Total amount 540 "Stefi" wrote: =MAX(0,C2-10)*1+MAX(0,C2-30)*2+MAX(0,C2-50)*3+MAX(0,C2-70)*10 Regards, Stefi €˛George A. Jululian€¯ ezt Ć*rta: Hi I need your help on this formula I need to calculate Storage Fees Column (A) Date In Column (B) Date out Column (C) numbers of days in Stock (A1-B1)=112days The First ten days free The fees on First 20 days after the 10days = 1 dollar The fees on second 20 days after the 10days and 20 day = 2 dollar The fees on Third 20 days after the 10days,20day and 20day = 3 dollar The fees on remaining days after the 10days, 20day, 20day, and 20 day = 10 dollar Date In Date out Numbers of days 05/11/2008 25/02/2009 112 Regards |
Excel Jululian
You are welcome! Thanks for the feedback!
Stefi €˛George A. Jululian€¯ ezt Ć*rta: Many Thanks on your great support "Stefi" wrote: Sorry, I didn't test it carefully. This is the right version: =MAX(0,MIN(20,$C$2-10))+MAX(0,MIN(20,$C$2-30))*2+MAX(0,MIN(20,$C$2-50))*3+MAX(0,C2-70)*10 Regards, Stefi €˛George A. Jululian€¯ ezt Ć*rta: Hi I made the calulation and result is 540 and not as formaul 872 10 X 0 = 0 20 X 1 = 20 20 X 2 = 40 20 X 3 = 60 42 X 10 = 420 Total days 112 Total amount 540 "Stefi" wrote: =MAX(0,C2-10)*1+MAX(0,C2-30)*2+MAX(0,C2-50)*3+MAX(0,C2-70)*10 Regards, Stefi €˛George A. Jululian€¯ ezt Ć*rta: Hi I need your help on this formula I need to calculate Storage Fees Column (A) Date In Column (B) Date out Column (C) numbers of days in Stock (A1-B1)=112days The First ten days free The fees on First 20 days after the 10days = 1 dollar The fees on second 20 days after the 10days and 20 day = 2 dollar The fees on Third 20 days after the 10days,20day and 20day = 3 dollar The fees on remaining days after the 10days, 20day, 20day, and 20 day = 10 dollar Date In Date out Numbers of days 05/11/2008 25/02/2009 112 Regards |
| All times are GMT +1. The time now is 04:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com