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

Simplify this formula

T

Thomas Kuriakose

Active Member
Dear All,

Kindly look at the below function and suggest a better way of writing this. Currently the desired result is correct, but would like to know whether this can be simplified.

=INDEX('Data Validation & Input Sheet'!$K$3:$K$9,MATCH('Cost Sheet Details'!D22,'Data Validation & Input Sheet'!$J$3:$J$9,0))+INDEX('Data Validation & Input Sheet'!$K$3:$K$9,MATCH('Cost Sheet Details'!D23,'Data Validation & Input Sheet'!$J$3:$J$9,0))+INDEX('Data Validation & Input Sheet'!$K$3:$K$9,MATCH('Cost Sheet Details'!D24,'Data Validation & Input Sheet'!$J$3:$J$9,0))+IF(D20<>0,'Data Validation & Input Sheet'!$M$3*D20,0)+IF('Cost Sheet Details'!D21<>0,'Data Validation & Input Sheet'!$M$4*D21,0)

Thank you so much,

with regards,
thomas
 
Dear Sir,

Kindly find attached the data. On preparing the data I found some errors in the formula reading FALSE and not giving the correct result.

Thank you so much for your kind help.

with regards,
thomas
 

Attachments

  • Cost Sheet.xlsx
    15.1 KB · Views: 2
Hi Thomas ,

The portion within your formula , which reads :

INDEX('Data Validation & Input Sheet'!$H$3:$H$9,MATCH('Cost Sheet Details'!D12,'Data Validation & Input Sheet'!$G$3:$G$9,0))+INDEX('Data Validation & Input Sheet'!$H$3:$H$9,MATCH('Cost Sheet Details'!D13,'Data Validation & Input Sheet'!$G$3:$G$9,0))+INDEX('Data Validation & Input Sheet'!$H$3:$H$9,MATCH('Cost Sheet Details'!D14,'Data Validation & Input Sheet'!$G$3:$G$9,0))

can be shortened to :

SUM(INDEX('Data Validation & Input Sheet'!$H$3:$H$9,N(IF(1,MATCH(D$12:D$14,'Data Validation & Input Sheet'!$G$3:$G$9,0)))))

This will have to be entered as an array formula , using CTRL SHIFT ENTER.

Narayan
 
Hi Thomas,

The easiest by creating a named range and I've tried the same in your sheet. Attached is for your reference and yes, you can also use the formula as suggested by Narayan which is also easier.

Kind regards,
A!
 

Attachments

  • Cost Sheet.xlsx
    17.2 KB · Views: 3
Dear Sir,

There are some errors when values in D10 & D11 are made zero, it is giving the result as False.

Kindly check and let me know how to get a value for this.

Thanks,

with regards,
thomas
 
Thomas Kuriakose

You are missing the last argument of first IF that should be zero in my opinion, modified formula should be.

=IF(D$10<>0,INDEX(tblP1[Price 2],MATCH('Cost Sheet Details'!$C$6,tblP1[Parameter1],0)*D10)+IF(D11<>0,INDEX(tblP1[Price 3],MATCH($C$6,tblP1[Parameter1],0)))*D$11,0)

Hope that helps.
 
Dear Sirs,

I am struggling to get this work due to the variables in the cells. I struggled for 4 hours still did not succeed.

The variables are as follows -
1. If D10=0,but D11>0,calculate value for D15, D16 & D17
2. If D10>0, but D11=0, calculate value for D15,D16 & D17
3. If D10, D11 are zero, all values should be zero
4. If D12=blank, IF D13=blank, but D14 is not blank, calculate value based on D14 entry and so on for all D12 and D13.

Basically all values are interdependent.

I have tried but have failed to get the desired result.

Kindly check the attached sheet.

thank you so much

with regards,
thomas
 

Attachments

  • Cost Sheet.xlsx
    15.2 KB · Views: 0
Please see attachment does it works, can you elaborate the last requirement further.
 

Attachments

  • Cost Sheet.xlsx
    15.1 KB · Views: 0
Dear Sir,

Thank you so much for your kind help.

I checked the file and found the following

-if value in D12 is blank, but if D13 & D14 is not blank, it is calculating only parameter for price 5, it should calculate Price 5 + value for D13 & D14.
-if D10 & D11 are zero all values should be zero, when I select value in D12, it computes value for D12 in D17.

Kindly check.

thanks,

with regards,
thomas
 
Please see attachment. i guess your first two requirement for all blank cells is fulfilled. Now please explain for the last one . There are three cells D12, D13, d14, what you want them to do.

Can you give me an example with correct result for the last calculation you are doing.
 

Attachments

  • Cost Sheet.xlsx
    14.8 KB · Views: 0
Dear Sir,

Thank you so much.

Please find attached the requirements for D12, D13, D14

Thanks once again,

with regards,
thomas
 

Attachments

  • Cost Sheet-1.xlsx
    16.9 KB · Views: 0
Dear Thomas,

Sorry for delay, i have some reservation and you can find details in attachment. I have made and table and tree diagram that will help you understand the scenarios/cases.
 

Attachments

  • Cost Sheet-1 (1).xlsx
    42.1 KB · Views: 0
Dear Faseeh,

Thank you so much for the detailed explanation and scenarios.

Please find attached the complete information.

Thank you so much.

with regards,
thomas
 

Attachments

  • Cost Sheet-1 (1-a).xlsx
    43.5 KB · Views: 0
Hi Thomas,

Please check values in Row 18 (yellow highlighted), i have tried to create all cases (1-7) with requirement of D10+D11, have a check if the results are correct. then we can move to the next step of retrieving values.

For next step i can't find the value in your Data Validation & Input Sheet say for
Case 3 you said "Total Value in D17 = 1000+1600 = 2600" where do you get these values from, please explain an example.
 

Attachments

  • Cost Sheet-1 (1-a).xlsx
    42.6 KB · Views: 0
Dear Faseeh,

For step 1, all are ok, only one suggestion, we have to consider one case where D10=0, and D10>0, and D11=0, and D11>0.

For step 2, the value 2600 is derived from -

(a) D12="", D13="', but D14 = L, value of L in DVIS sheet = H5= 1600 (Price 4)
(b) and since value in D10=1 and D11=1, the values for Price5(J4+J5) = 1000

(c) and hence total value = 1000+1600 = 2600

Thank you so much.

with regards,
thomas
 
For step 1, all are ok, only one suggestion, we have to consider one case where D10=0, and D10>0, and D11=0, and D11>0.
Click to expand...

This will not make any difference. Please test it.

Now what we need to with D17 it has already a formula.. Sorry i lost focus.
 
Dear Faseeh,

Thank you so much for the support on this.

I tested, and am attaching the file, if you see D14 is non blank in all cells. The total value in D17 should be from Price 5 (depending on selection of D10 &D11) + Value of J from Price 4, which in this case is 1500. Result 2500 for selections where D10 and D11 are greater than zero. But the result is only from Price 5.

with regards,
thomas
 

Attachments

  • Copy of Cost Sheet-1 (1-b).xlsx
    43.9 KB · Views: 0
Dear Faseeh,

(1) Price & Price 1 is for D15 - Here the formula is ok
(2) Price 2 & Price 3 are for D16 - Here also the formula is ok
(3) Price 4 & Price 5 is for D17 and here we have the problem if D12 is blank, if D12 is not blank then all values are getting computed.

But a user could use only value in D13, then the formula does not work, or he/she could use on D14, then also the formula does not work for Price 4.
This is working when first selection is D12 followed by D13 and D14.

Thank you so much.

with regards,
thomas
 
Hi Thomas

Please see if computation in row 18 is correct.
 

Attachments

  • Copy of Cost Sheet-1 (1-b) (1).xlsx
    42.9 KB · Views: 0
You must log in or register to reply here.
Back
Top