Hi
Thank you for your support, the average rate per day amount*12/365 disregards whether the month is 30 or 28 31,
I will keep trying to get it worked.
Thank you
Hi
Thank you for your support, the average rate per day amount*12/365 disregards whether the month is 30 or 28 31,
I will keep trying to get it worked.
Thank you
Hello
The average rate per day applies in both leap and regular years,
The rate per day into number of fraction days of the month, formula becomes void when it is full month it pays full amount without pro-rated fractions.
ROUND(DAY(E7)/DAY(EOMONTH(E7,0))*E9,2), the formula, you helped with, is so valuable, but unfortunately didn't work with the rate per day.
Thanks & Regards
Hi
30.41666 is the average amount; amount*12/365
Thanks
Hello
Thank you for your prompt reply
It works logically when I used it, but didn't match my calculation as the rate per day should be E7/30.41666, and not E7/30, which gives high difference . Thank you
Hello
I am creating a formula to capture a rate per day * number of days of the month, but when it is a full month not to apply the formula
I mean if it is 28 Feb or 30 or 31 not to calculate the rate per day into Number of days, but to use the amount as is.
E7 Date
E9 Amount,
C13 Number of days of the month (fraction of the month) ; formula of CONCATENATE(DAY(E7)," days"
I used below formula
=IF(DAY(EOMONTH(E7,0))>=30,E9,((LEFT(C13,2)*(E9*12/365))))
the result is always full amount
and it is not working when it is Feb 28 days?
How to make the "EOMonth" changeable based on the month in E7 date, then how to make the calculation correct, either full amount on full month or pro-rated by number of days
Appreciate your help
Thank you
Wafaa
Re: if , any nested formula
Quote from NBVC;688405Or... =INDEX($A$1:$C$1,SUMPRODUCT(($A$2:$C$20=H4)*(COLUMN($A$2:$C$20))))
Yes, this formula works more than the above one.
Appreciated.
Re: if , any nested formula
may be i didnt know how to express what i want.
attached is a revised excel sheet.
i am unable to use "if" formula to select any item of a list
hi
i have 3 lists, i want them validated but shown in one cell.
col. A have 5 bill types, (Validated "Bill")
col. B have 28 product types, (Validated "product")
col. C have 12 material types, (Valided "Material")
i want in
F2 to select any of the 3 lists.
G2 to recognise them by the validation title.
H2 to categorise them payable or receivables:
attached is the example.
Thank you
hi
i am trying to get the number of days from last time ticket paid till todate, considering the day & month of the hiring date but the year of the last time paid.
say
AAA hired on 15-Nov-2001 receives a ticket every 24 months; received last ticket in Nov 2011.
i want to count
the number of days between 15-Nov 2011 until 31-Jul-2013 (dd & mm are from joining date yyyy from last time paid)
i have a list of 1200 names with different dates of joining and different dates of payments.
thank you
Re: sum if in combination to vlookup
hi
thank you for your help, but i didnt know how to extract the information from the pivot table to the main sheet i work on,
the main sheet is in sheet 1 where i have a main table and need to add the loans outstanding amounts. i rely much on vlookup..
i want
vlookup the client number of sheet 1, in the loan sheet, to extract the loan code 53 outstanding, and in another cell in sheet 1 to extract the loan code 56 and so on..
(pls see attached sheet).
thank you
Re: sum if in combination to vlookup
Thank you,
but this gives the total outstanding amounts for the one client, i want to have the outstanding per loan,
for example client 4810 has two different loans, the said formula totaling up both, how to separate them? and have the answers in to separate cells. one for loan 53 and the other for loan 56.
thx again
I have a table of several columns
Column A client code
Column B outstanding loan amount
column c type of loan numeric code
I have a list of client codes, and want to vlookup the code and sumif the loans referred to that client in a particular loan code.
thank you