In the attached file please see cell Z17 where formula showing wrong result and not working perfectly it is showing value 1133974 but it should be 1142244
in that Z column elsewhere formula work perfectly but only in this cell Z17 it is not working perfectly
how this happen please tell me the solution
You need to show why the value is wrong i.e. how do you arrive at 1142244
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
i want multiplication of cell E15*W17=1142244 it means cell E15 value 45709.90 * W17 value 24.989 = 1142244 in that Z column you can see in cell Z16 there is perfect result but in Z17 formula not working Z16 is previous cell of Z17 where is formula working in cell Z16 but not work in cell Z17
With this ....
It will not find any cell value PREVIOUS [ i.e. E15 ] to row 17: you need to review your "logic" for this MATCH. The other results are correct (almost)! by chance as there is no correlation between the data in N:W and the QTY field required.
INDEX($E17:$E$297995,MATCH("QTY",$D17:$D$297995,0)-1)
I suspect other results are also wrong because the formulae are using the wrong "QTY".
Last edited by JohnTopley; 11-17-2023 at 07:13 AM.
Do you suggest me any solution for my problem if you do not understant my problem i once again do clear for you see formula is working fine in this cells Z8, Z9, Z10, Z11, Z12, Z12, Z13, Z14, Z15 cell value of W8 multiplied by cell value of E15, cell value of w9 multiplied by E15 , cell value of W10 multiplied by cell E15 and so on in Cell Z16 and Z17 formula not working
I understand the problem and I explained in post #5 why your formula does not work.
Select Z17 then "Formulas" on ribbon then "Evaluate formula" and you will (hopefully) see the problem i.e. the wrong QTY is selected because of the MATCH "error"
**** You need to learn how to "Evaluate Formulas" ****
See attached column AA from Row 8 and from row 68
Last edited by JohnTopley; 11-17-2023 at 07:49 AM.
The formula in cell Z17 returns 1133974.45, because E17*E34 = 1133974.45
i changed "QTY1" to "QTYn" but did not get disired result check attached file
What I meant was change was change "Qty1" to "Qty2", "Qty3" etc in the formula for the appropriate table,
Formula:
=IF(ISNUMBER(N8),IF(ISBLANK(X8),W8*INDEX($E$8:$E$297567,MATCH("QTY1",$D$8:$D$297567,0)-1),X8*INDEX($E$8:$E$297567,MATCH("QTY1",$D$8:$D$297567,0)-1)),"")
Formula:=IF(ISNUMBER(N8),IF(ISBLANK(X8),W8*INDEX($E$8:$E$297567,MATCH("QTY2",$D$8:$D$297567,0)-1),X8*INDEX($E$8:$E$297567,MATCH("QTY2",$D$8:$D$297567,0)-1)),"")
etc .....
What is the point of giving you a w/book sample if you cannot be bothered to look at it!
Last edited by JohnTopley; 11-18-2023 at 12:24 PM.
If i need to do changes in every formula it is very time consuming and that formula also become useless because formula made for automation the task if I need to change it manually instead of that I do calculation manually
Well nothing I can do about how your data is formatted: If you cannot establish a connection between the "Qty" / "Rate" data and the corresponding data in N:W, how do expect ANY formula to know what data to use?
The tables in N:W are dynamic (Varying size) so your original approach will (does) not work.
Don't shoot the messenger!
You only need to change the FIRST formula in a N:W "block" and then drag it down. Is it any hardship to put the formula into the appropriate cell in Column Z when you create a new table and change the "QTY" value in the formula?
You could change it in blocks using Find & Replace.
You have been a member long enough to realise that this thread title was pretty poor: titles are meant to tell us what yo uare trying to do. not that something isn't working (which is at best generic). Please make a better effirt in this respect next time.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks