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

Combined Formula

K

keanu

New Member
Hi Excel community,


I have four (4) worksheets:


1.ACTUAL 2012

This sheet contain finalized number done by finance team


2.RESERVED AMOUNT

This sheet contain database that generate from system on daily basis and the data keep changes based on the current date. At the bottom, there is a grand total for every stuff listed on this database


3.REVENUE AMOUNT

This sheet also contain database that generate from system on daily basis and the data keep changes based on the current date. At the bottom, there is a grand total for every stuff listed on this database


4.ANALYSIS AUG 12

I want to use this sheet as analysis of the trend of the product selling thru customer


I want to know if there is any combined formula that I can apply to ANALYSIS AUG 12, so that it can refer to those three worksheet (ACTUAL 2012, RESERVED AMOUNT & REVENUE AMOUNT). For example:


On ANALYSIS AUG 12 (RESERVED AMOUNT) my cursor is on D4 and if there is actual figures on ACTUAL 2012, then it will refer to the figures but it there is NO actual figures then it will refer to RESERVED AMOUNT


Right now, I used equal sign (=) when I received ACTUAL figures from finance, but for the future date I used below formula:


IF(ISERROR(INDEX('[08 AUG OTB RM.xls]24Aug'!$AB$5:$AM$600,MATCH(Z$5,'[08 AUG OTB RM.xls]24Aug'!$AA$5:$AA$600,0),MATCH($A6,'[08 AUG OTB RM.xls]24Aug'!$AB$4:$AM$4,0))),"",INDEX('[08 AUG OTB RM.xls]24Aug'!$AB$5:$AM$600,MATCH(Z$5,'[08 AUG OTB RM.xls]24Aug'!$AA$5:$AA$600,0),MATCH($A6,'[08 AUG OTB RM.xls]24Aug'!$AB$4:$AM$4,0)))


Here is the link to the worksheets:


https://skydrive.live.com/?cid=759EAD333A18E1E5&id=759EAD333A18E1E5!111
 
Hi Kaushik ,


This is a problem with Skydrive ; don't click on the link itself , since it does not include the entire URL ; copy the complete URL , and paste it in your browser ; you will see all the 4 files , in the form of a .zip file.


Narayan
 
@Narayan...Thank you for your guidance. I am now able to download the file.


Hi keanu,


Before we go ahead and come up with exact formula, let me first understand the query.


At sheet1 D4 (of ANALYSIS AUG 12 workbook for reserved amount), the formula should do the following:


(for telephone and Aug-1-2012, if the formula finds a number >0 at col E of ACTUAL 2012 workbook then the number should be picked up else(in case the first condition is false i.e number = 0) the formula should pick the number from reserved amount workbook that matches the date(Aug-1-2012) and the item(telephone). The same is applicable for reserve amount of ANALYSIS AUG 12 workbook while for the second condition the formula should look at the revenue amount workbook instead of reserved amount workbook.


If my understanding is correct, then please note the following:

If the first condition is false(number is found to be zero in ACTUAL 2012 workbook), then the formula will return you nothing (for any item in ANALYSIS AUG 12 workbook) for the date ranges from 1st AUG to 22nd AUG as the data starts in both reserved and revenue amount workbook from 23rd AUG onwards.


Please correct me if my understanding is wrong.


@Narayan: Request you to please chip in if you have already understood the query clearly(needless to say:)).


Kaushik
 
Hi Kaushik,


Sorry for explanation. I tried to understand your logic, and just to add:


If there is actual number in ACTUAL 2012 for SOLD item Telephone (Column C6), then on sheet1 D4 (ANALYSIS AUG 12) will return to 24, but if there is no actual figures on C6 (ACTUAL 2012) then it should refer to RESERVED AMOUNT Worksheet by matching date and product name (telephone).


Hope this will help you. let me know if you need further information.


Thanks and regards,


Rico
 
Hi keanu,


By "No Figure" you means a zero or a blank Cell? There are zeros but no blanks in the actual sheet!


Regards!
 
Hi keanu,


Thank you for the explanation.


I am now posting from my office network and not able to open the file due to firewall policy.


I will definitely get back to you by tomorrow morning.


Thank you for your patience.


Kaushik
 
Hi keanu(Rico),


As promised, here is the file with consolidated formula for your requirement.


https://hotfile.com/dl/168836793/be4ce59/04._ANALYSIS_AUG_12_Consolidated_formula_Rico_Chandoo_Forum.xls.html


Please note the following:


1)In a single workbook I have used your three datasets (otherwise I would have to upload three workbooks)

i)'Analysis sheet'- here we have your 'ANALYSIS AUG 12' data , both reserved amount and revenue amount(However, I have worked only on reserved amount)

ii)'Actual 2012 sheet'- here we have your 'ACTUAL 2012' data

iii)'Reserved amount sheet'- here we have your 'Reserved amount' data


2)I have done a little rearrangement of your dates position (in Actual 2012 sheet) in order to make the 'sumproduct' formula working.


Please check and let me know if I am able to meet your requirement.


Sorry for delayed reply....


Regards,

Kaushik
 
@SirJB7,


Good afternoon,


You are absolutely right...I could do that as well.


But at the first place, I just wanted to see if the solution / formula is what Rico is exactly looking for. If yes, he can easily replicate the same considering the data in multiple workbooks.


And, in fact, I also find it little easy while keeping the data in multiple sheets of same workbook :)


Wish you a very happy weekend Pablo Sir...


Regards,

Kaushik
 
Hi Kaushik03,


Thank you for getting back with the formula. I will try to apply it on my spreadsheet and will let you know the update again later. Sorry for the delay, as i was a bit busy with paper work yesterday.


Thanks and regards,


Keanu
 
Hi Kaushik,


it works properly on 1 column, when i tried to copy and paste down it shown Error (#VAlue!). i checked your worksheet and adjust the formula that i apply for my sheet. Below is the formula I used:


IF(SUMPRODUCT(('[ACTUAL 2012.xls]AUG'!$C$3:$CQ$3=$C6)*('[ACTUAL 2012.xls]AUG'!$B$7:$B$17=$B7)*('[ACTUAL 2012.xls]AUG'!$C$7:$CQ$17))>0,SUMPRODUCT(('[ACTUAL 2012.xls]AUG'!$C$3:$CQ$3=$C$5)*('[ACTUAL 2012.xls]AUG'!$B$7:$B$17=$B7)*('[ACTUAL 2012.xls]AUG'!$C$7:$CQ$17)),SUMPRODUCT('[08 AUG OTB RM.xls]28Aug'!$AA$5:$AM$130=$C$5)*('[08 AUG OTB RM.xls]28Aug'!$AB$3:$AM$3=$B7)*('[08 AUG OTB RM.xls]28Aug'!$AA$5:$AM$130))


Other thing, the date on reserved amount sheet always changing as it is generated from system on daily basis after finance finalized the figures. So i wonder if this formula can work when the date is changing on reserved amount sheet.


Please advice.


Thanks and regards,


Keanu
 
Hi Keanu,


I believe problem is with the referencing that you have used in the formula.


Few things I am not able to understand:


1)Look at this part: SUMPRODUCT(('[ACTUAL 2012.xls]AUG'!$C$3:$CQ$3=$C6


I believe C3:CQ3 is your date range and and C6 contains one of your listed items (Book or orange etc.). If this is the case then formula should obviously return error.


Moreover, I could see that you have referred a workbook called "08 AUG OTB RM.xls" which is something different from whatever the workbooks you uploaded in your first post.So, it is little difficult for me to track the error without looking at this workbook.


I suggest you to cross check all the reference that you have used in the formula. However, meanwhile please upload the files that you have used to write and modify the formula. I will check and get back to you.


And change in the date should not be a problem as long as we give the proper refernce in the formula.


Regards,

Kaushik
 
Hi Kaushik03,


I found the answer :), as i need to unlock dollar sign ($) so that i can copy paste down. However i found challenge as when the actual number is zero "0" then the formula return to "#Value!", how can i solve this? if there is "0" zero then the formula result = to "0" instead of "#value". Fyi, i'm using excel 2003.


it is different with your worksheet, even there is a zero "0" in the ACTUAL sheet but it still give the result as zero "0" instead of value


Note: the data is confidential therefore, i'm using another example but the format remain the same. Hope you can understand.


Looking forward to hearing you again.


Thanks and regards,


Rico
 
Hi keanu,


Glad to hear that the problem is solved.


Regarding #value...you can wrap the formula with IFERROR.


Syntax for IFERROR:

IFERROR(value, value_if_error)


1) Value Required. The argument that is checked for an error.

2)Value_if_error Required. The value to return if the formula evaluates to an error. The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.


For e.g., IFERROR(IF(SUMPRODUCT(('[ACTUAL 2012.xls]AUG'!$C$3:$CQ$3=$C6)*('[ACTUAL 2012.xls]AUG'!$B$7:$B$17=$B7)*('[ACTUAL 2012.xls]AUG'!$C$7:$CQ$17))>0,SUMPRODUCT(('[ACTUAL 2012.xls]AUG'!$C$3:$CQ$3=$C$5)*('[ACTUAL 2012.xls]AUG'!$B$7:$B$17=$B7)*('[ACTUAL 2012.xls]AUG'!$C$7:$CQ$17)),SUMPRODUCT('[08 AUG OTB RM.xls]28Aug'!$AA$5:$AM$130=$C$5)*('[08 AUG OTB RM.xls]28Aug'!$AB$3:$AM$3=$B7)*('[08 AUG OTB RM.xls]28Aug'!$AA$5:$AM$130)),0)


This will return you 0 wherever you have error (#value) [considering your formula is working perfectly well. Otherwise this may lead to false positive in the case where you have an error for some genuine reason]


Hope this helps..


Kaushik
 
Hi Rico,


My apologies!!


I missed the part that you are using excel 2003.


In excel 2003, IFERROR function does not work. You need to use something like this:


IF(ISERROR(Your formula),0,Your formula)


It is besically saying excel that IF your formula gives any error then wrap it with (or return me) 0(zero) ELSE return the result of your formula.


Hope it helps.


Regards,

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