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

convert excel formulas from 2010 to 2003 with explanation

T

tpsdas

New Member
i hv found a xls file on inventory in net , which i want to modify & use in my inventory xls like follows:
(1) xlfn.COUNTIFS(Orders_and_Inventory!$L$14:$L$19,"<0",Orders_and_Inventory!$D$14:$D$19,">"&TODAY(),Orders_and_Inventory!$C$14:$C$19,"Sale")
(2)xlfn.SUMIFS(Orders_and_Inventory!$G$14:$G$19,Orders_and_Inventory!$D$14:$D$19,"<="&Orders_and_Inventory!$D14,Orders_and_Inventory!$F$14:$F$19,Orders_and_Inventory!$F14,Orders_and_Inventory!$C$14:$C$19,"Purchase")-_xlfn.SUMIFS(Orders_and_Inventory!$G$14:$G$19,Orders_and_Inventory!$D$14:$D$19,"<="&Orders_and_Inventory!$D14,Orders_and_Inventory!$F$14:$F$19,Orders_and_Inventory!$F14,Orders_and_Inventory!$C$14:$C$19,"Sale")
(3)INDEX(Tbl_Current_Inventory,MATCH(H5,INDEX(Tbl_Current_Inventory,,1),0),3)
(4)xlfn.IFERROR(_xlfn.SUMIFS(Orders_and_Inventory!$G$14:$G$19,Orders_and_Inventory!$D$14:$D$19,">"&TODAY(),Orders_and_Inventory!$F$14:$F$19,F4,Orders_and_Inventory!$C$14:$C$19,"SALE"),"")

My Qts are:
1) how to convert these formulas from excel 2010 to excel 2003 so that i can use
2) attached a zip file where i hv these formulas , so U can understand clearly what I am asking for .
thanks
 

Attachments

You should be able to use sumproduct:
1) =SUMPRODUCT(($L$14:$L$19<0)*($D$14:$D$19<=TODAY())*($C$14:$C$19="Sale"))

Going into meeting now, but will take a look at the rest later if someone else doesn't get to it.
 
2) =SUMPRODUCT(--($D$14:$D$19<=$D14)*($F$14:$F$19=$F14)*($C$14:$C$19="Purchase"),$G$14:$G$19)-SUMPRODUCT(--($D$14:$D$19<=$D14)*($F$14:$F$19=$F14)*($C$14:$C$19="Sale"),$G$14:$G$19)

3) INDEX,MATCH should work with Excel 2003

4) This I give you as homework.
Use SUMPRODUCT for SUMIFS part and use IF,ISERROR to replace IFERROR
EX: =IFERROR(CalcOperation,Output) will become
=IF(ISERROR(CalcOperation),Output,CalcOperation)

I'm seeing more cells with IFERROR & COUNTIFS than you listed in your post.

See attached with calculation update for B8 & L14:19. Also corrected error in K5

If you need further help, let me know.
 

Attachments

Thank you sir for the help. Really delighted !!! I am using it and working on my project. It is helping me a lot. Once again Thanking the Chandoo team and specially Chihiro for the help.
Now i have few problems:
I have attached the file - Marked and coloured in the file where I need help. Requesting to kindly go through the zip file and help me.
(1) Under Products Where Current Inventory <= Re-Order Point, please help me to convert in Excel 2003. The formula is as follows
=_xlfn.IFERROR(INDEX(Tbl_Current_Inventory,SMALL(IF(INDEX(Tbl_Current_Inventory,,5)<=0,ROW(INDEX(Tbl_Current_Inventory,,5))-ROW(Help!$D$3),""),$L$5-ROW($H$4)),1),"")

(2) Commands and where and how to use this Spin Bottom of Control Tool box to work.
Eagerly waiting for the help. Thanks in advance.

Thanks in advance.
 

Attachments

1) This should work.
Code: 
=IF(ISERROR((INDEX(Tbl_Current_Inventory,SMALL(IF(INDEX(Tbl_Current_Inventory,,5)<=0,ROW(INDEX(Tbl_Current_Inventory,,5))-ROW(Help!$D$3),""),$L$5-ROW($H$4)),1)),"",(INDEX(Tbl_Current_Inventory,SMALL(IF(INDEX(Tbl_Current_Inventory,,5)<=0,ROW(INDEX(Tbl_Current_Inventory,,5))-ROW(Help!$D$3),""),$L$5-ROW($H$4)),1))
NOTE: As CSE. Also each row after top most, you need to +1 after $L$5

2) AFAIK - Spin button control should work for 2003. But I don't have copy of 2003 to test.
 
Thanks Chihiro ji again for prompt reply and that too in Sunday!!!
I feel the above command you have provided is not working. And frankly speaking to decode this long formula is beyond my capacity( sorry for my ignorance!!)
I am attaching my actual worksheet ( in Excel 2003) where I am working for a medical inventory for a semi charitable medical dispensary.
Please looking to "Stockposition" tab of the attached file, where I am facing the trouble. There I want to find the Product names where Current Inventory <= Re-Order Point and also help me on the spin button to work.
Please look into my original workbook and Requesting to Correct and Guide me.
Feeling ashamed to disturb multiple times due to my ignorance....but........!!!
 

Attachments

Taking a quick look. One of the issue is that you are not using named table for your inventory data. In the original sheet you provided at beginning of the thread, the table is contained in hidden "Help" sheet.

2nd. You are missing "=" sign in h6. The formula also need to be entered as array (CSE = Control + Shift + Enter).

As for Spin control, if you look at the formula bar for the control. You will see "=L5", in order for the spin control to work with set up, you need "5" in L5.

You really need to understand how Original works before you start customization.
 
Sir, you are 100% correct , I have missed the table containing the hidden "Help" sheet and also did not end the formula with Control + Shift + Enter and also needed to understand fully the Original work before customization..
My small request:
1) Please look into my actual work sheet "Stockposition" tab of my actual worksheet (attached ), where I am facing the problem. There I want to find the Product names where Current Inventory <= Reorder Point . I kindly request you to make the formula workable for me. I am not able to decode this big formula .

2) Please also make the spin button workable in my actual working sheet ( not the file from where i have taken the idea)
I have attached my actual working sheet. Please look into it.

After these two problems are solved I would like to clarify some other doubts for my future use and for enhancing my knowledge
Pleaseeeee Heeeelpppp...........
 

Attachments

Absolutely No problem sir..please take your time....
I have also planned a way around to insert a new sheet of Purchase indent where the Current Inventory <= Reorder Point ., will also work for me.
 
See attached.

The spin control is used to control L5. Each time you hit down, it adds 1 to value stored in L5 (default being 5). In turn formulas in H6:H10 should reference value in L5.

As spin control updates value in L5, calculation results in H6:K10 should update.
 

Attachments

Once again Thanks ..& thanks...& .......a great rendered to me...
It is working perfectly fine.
Under Product names (Current Inventory <= Reorder Point ), the formula you have given is :

IF(ISERROR(INDEX(Stockposition!$B$14:$O$108,SMALL(IF(INDEX(Stockposition!$B$14:$O$108,,14<=0,ROW(INDEX(Stockposition!$B$14:$O$108,,14))-ROW($J$13),""),$L$5-ROW($H$4)),1)),

I need few more helps:
(a) kindly explain the above formula in steps. (Not clear to me)
(b) How are you “naming the table – “Stockposition”

(.C) a blue line and * is appearing at the bottom of the table area which is enabling to type with out dragging the formula and also adding new line – how it is done – please tell me.( plz refer the earlier attachment you have altered and attached for my help)

Your help will enable me to complete the major hurdles I was facing in this Inventory package….So once again lots of Thanks to Mr. Chihiro and the Chandoo team for the help.
I kindly request Chihiro to answer my above queries when ever you are free
 
a) Let me get back to you on this... I'll have to think on best way to break it down

b) I didn't name the table "Stockposition" that's the Sheet name. However, you can name tables by selecting any portion of the table and going to Table Tools tab in Ribbon menu, and at left most part of the Ribbon, you can type in Table Name (note that you need to format data as table beforehand, using steps explained below)
upload_2015-8-11_21-29-15.png

c) You'd select data which you want to convert to table format (must have single header row at top and data below), and then at Home tab in the Ribbon menu, select "Format as Table"
 
yess..sir.....naming & blue line and * part is clear to me......
only thing left is question (a)
Under Product names (Current Inventory <= Reorder Point ), the formula you have given is :
IF(ISERROR(INDEX(Stockposition!$B$14:$O$108,SMALL(IF(INDEX(Stockposition!$B$14:$O$108,,14<=0,ROW(INDEX(Stockposition!$B$14:$O$108,,14))-ROW($J$13),""),$L$5-ROW($H$4)),1)),

(a) kindly explain the above formula in steps. (Not clear to me)

Dear Chihiro take your time, but please clear my doubt. Answer my above queries when ever you are free!!
 
Sir Chihiro,
In my actual sheet I am facing some major problems. Please help.

1) Please see sheet "Stockposition" – under “Medicine Where Current Inventory <= Re-Order Point” , the values are NOT coming properly. Please note in the present context Albuterol and Amoxicillin should appear in 1st and 2nd line instead Atenolol , Cephalexin is showing respectively.

Since this area I did not understand, I am not able to rectify it. Please look into the matter and solve it for me.
I further request you to explain it step wise when you have the time.

2) Also PO tab – Purchase Indent - proper medicine names are not appearing - please help.

3)Also I feel the overall program has become little slow – any help for this??
 

Attachments

1) You added a column to the table. Therefore you need to change column 14, reference to column 15.
To explain this formula is difficult as it utilizes multiple array... how familiar are you with Index formula and array?

I'll try to set aside some time, but it will take a bit.

2) The formula is only capable of looking at first occurrence of the condition as is. By tacking on +ROW(1:1)-1 will adjust accordingly.

3) Not much can be done here. 2003 limitation will not allow for more efficient formula. However, you can limit reference table/array size to speed it up a little.
 

Attachments

Once again THANKS for the instant help and reply. I feel ur specially is the promptness !!!
1) My 1st problem is solved by you. I know little about Index formula and array - not in depth. I dont have any knowledge about Small or ROW(1:1)-1
2) Please refer your earlier xls sheet . In PO tab , I want to find the names of the medicines only where Purchase Indent Qty>0. I feel the formula you have given needs little modification. e.g medicine name in 1st row "Albuterol" - PO qty is 54 , but the name is not appearing. This formula is working fine when Reorder point>0,but I want the medicine Names when Purchase qty>0
Please look into this kindly - when your time permits.
 
Last edited:
1) Ok, will work on it. Hopefully I have something for you in few days

2) Original formula was referencing Column 15 to look for where Helper Colum <=0
=INDEX(Stockposition!$B$14:$P$108,SMALL(IF(INDEX(Stockposition!$B$14:$P$108,,15)<=0,ROW(INDEX(Stockposition!$B$14:$P$108,,15))-ROW(Stockposition!$K$13),""),Stockposition!$M$6-ROW(Stockposition!$H$4))+ROW(1:1)-1,1)

Since, Purchase Indent Qty is in column 12 just change that portion and look for >0
=INDEX(Stockposition!$B$14:$P$108,SMALL(IF(INDEX(Stockposition!$B$14:$P$108,,12)>0,ROW(INDEX(Stockposition!$B$14:$P$108,,12))-ROW(Stockposition!$K$13),""),Stockposition!$M$6-ROW(Stockposition!$H$4))+ROW(1:1)-1,1)

Both formulas are entered as array (CSE).
 

Attachments

Thanks once again for the prompt reply. The New solution is working fine. Only thing I notice when Purchase Indent Qty=0, then also it is showing medicine names. I want to get the medicine names only when Purchase Indent Qty >0 (don't want to hide the rows manually where it is 0). Please HELP
 
Oh, my bad. I miss read your post.
Use below and enter as array (CSE).
=IF(ISERROR(INDEX(Stockposition!$B$14:$M$108,SMALL(IF(Stockposition!$M$14:$M$108>0,ROW(Stockposition!$M$14:$M$108)-ROW($K$13)),ROW(1:1)),1)),"",INDEX(Stockposition!$B$14:$M$108,SMALL(IF(Stockposition!$M$14:$M$108>0,ROW(Stockposition!$M$14:$M$108)-ROW($K$13)),ROW(1:1)),1))
 
WWwaaaaooooo!!!!..yesss...it works........thannkkk UUUUuuuuuuu

Only thing as of now left is explanation...when you get time please help.
 
You must log in or register to reply here.
Back
Top