Hi sir, may i ask for your help suggesting a formula for below criteria. Requirement is the business starts on day 1 and from day 1 we have a unit price for goods, lets say it goes from 4 units on day 1 and 6 units on day 2 and 10 units on day 3. When the price of the goods move by 10 or above, then we should mark it as Yes. So ideally against day 3, we should mark yes (possibly with if logic) But next part gets complicated for me From day 4, the requirement is not for marking yes for above 10, but it should compare 10 units movement from the previous yes point (day3). Lets say day 4 price of goods move to 18 then it should be flagged as no. But on day 5 if price moves to 20, then we should have the yes flag because difference in day 3 and day 5 is 10 or more. Pls help
Hello @ammudolly2250 ! I found a solution that can work but requires multiple columns in order to simplify the functions in each column. Suppose the unit price is in 'Column B' you will want to paste these functions to match the second date in which there is a unit price and assuming headers. Column C will have the function "=LEN(B3)" ---- Column D with have the function "=IFS(C3=1,"",C3=2,LEFT(B3,1),C3=3,LEFT(B3,2),C3=4,LEFT(B3,3))" ---- This will only work up to 9999 as a unit price ---- Column E will have the function "=IF(D3>D2,"Yes","")" The first few rows may populate with "Yes" as a mistake but after you delete the mistakes Column E should show "Yes" when the unit price moves to the next increment of 10.
Using multiple variables/parameters is a way to create value ranges for your cells, and can help create dynamic Excel Worksheets!
Hi sir, may i ask for your help suggesting a formula for below criteria.
Requirement is the business starts on day 1 and from day 1 we have a unit price for goods, lets say it goes from 4 units on day 1 and 6 units on day 2 and 10 units on day 3.
When the price of the goods move by 10 or above, then we should mark it as Yes. So ideally against day 3, we should mark yes (possibly with if logic)
But next part gets complicated for me
From day 4, the requirement is not for marking yes for above 10, but it should compare 10 units movement from the previous yes point (day3).
Lets say day 4 price of goods move to 18 then it should be flagged as no.
But on day 5 if price moves to 20, then we should have the yes flag because difference in day 3 and day 5 is 10 or more. Pls help
Hello @ammudolly2250 ! I found a solution that can work but requires multiple columns in order to simplify the functions in each column. Suppose the unit price is in 'Column B' you will want to paste these functions to match the second date in which there is a unit price and assuming headers. Column C will have the function "=LEN(B3)" ---- Column D with have the function "=IFS(C3=1,"",C3=2,LEFT(B3,1),C3=3,LEFT(B3,2),C3=4,LEFT(B3,3))" ---- This will only work up to 9999 as a unit price ---- Column E will have the function "=IF(D3>D2,"Yes","")" The first few rows may populate with "Yes" as a mistake but after you delete the mistakes Column E should show "Yes" when the unit price moves to the next increment of 10.
Improve video quality