User Profile
lauradelfosse
Copper Contributor
Joined 6 months ago
User Widgets
Recent Discussions
Calculate based on number between text and search on text
Hello everyone, I have a question, I'm not sure if it is possible to do so, but I'll shoot my shot in asking. I added the excel-file. I already asked ChatGPT but, same as last time, the answers it gives me aren't compatible with either the version I use or it isn't what I'm asking for. So, here's the deal: In column G under "Berekeningen" you'll see the next cells: Totaal aantal interventies Gemiddeld aantal interventies per week Totaal aantal installaties Gemiddeld aantal installaties per week I would need the following: Totaal aantal interventies: In the range filled in using conditional formatting, search for where the words "AANWEZIG" and "INI" are. There is a number written between both words, a sum must be made of all these numbers. Gemiddeld aantal interventies per week: Calculate the average per week of the above, so in other words:search for where the words "AANWEZIG" and "INI" are. There is a number written between both words, a sum must be made of all these numbers and the total of this must be divided by the number of weeks, in this case 9 weeks. Totaal aantal installaties:In the range filled in using conditional formatting, search for where the words "AANWEZIG" and "INS" are. There is a number written between both words, a sum must be made of all these numbers. Gemiddeld aantal installaties:Calculate the average per week of the above, so in other words:search for where the words "AANWEZIG" and "INS" are. There is a number written between both words, a sum must be made of all these numbers and the total of this must be divided by the number of weeks, in this case 9 weeks. This is the range using conditional formatting: =$B$3:$F$8;$B$10:$F$15;$B$17:$F$22;$B$24:$F$29;$B$31:$F$36;$B$38:$F$43;$B$45:$F$50;$B$52:$F$57;$B$59:$F$64 For info, my Excel only accepts European (Belgian-written) formulas. The version used is version 2407 (build 17830.20138) and is included in the Microsoft 365-apps for businesses. Here's an example of a formula ChatGPT gave me: Totaal aantal interventies: =SOMPRODUCT(--(ISGETAL(VIND.SPEC("AANWEZIG"; B2:F1000))), --(ISGETAL(VIND.SPEC("INI"; B2:F1000))), --(DEEL(B2:F1000; VIND.SPEC("AANWEZIG"; B2:F1000)+9; VIND.SPEC("INI"; B2:F1000)-VIND.SPEC("AANWEZIG"; B2:F1000)-9))) Gemiddeld aantal interventies per week: =ALS(AANTAL.ALS.UNIEK(WEEKNUM(B1:F1))=0; 0; (Totaal_aantal_interventies)/AANTAL.ALS.UNIEK(WEEKNUM(B1:F1))) Due to which I received the following faults: "#WAARDE!" and "#NAME?" I even tried with "help columns" but it didn't work either. I also tried using VBA with a code ChatGPT gave me, but sadly I lost it so I can't paste it here. Can anyone tell me if what I ask for is possible, and if so, how to obtain it? I would need to copy paste the formula for another range (the next two following months, which I haven't created in the file yet). Thanks in advance!Solved792Views0likes4CommentsIndex or Lookup? Subtraction and conditional formatting.
Hi! I would like to automatically calculate the remaining inventory based on the selected product and the quantity entered. The inventory data is located in a separate table. For example: C3: Product D3: Quantity E3: Where the formula should be placed I2:I24 : Range where inventory quantities are located J2:J24 : Range where product names are located. C3 is a dropdown-list based on range J2:J24. The formula should do the following: - Find the match of C3 in J2:J24 - Check the inventory quantity from the match of C3 in I2:24 - Subtract the amount in D3 with the inventory quantity Adding to that I would like to add conditional formatting that if the value of E3 is smaller or equal to 0 than the inventory quantity (I2:24) of that same product (C3 and J2:J24) the color of the cell changes to red. Is this possible? I tried with ChatGPT butunfortunately, the formulas I received are not correct or not applicable in my case, and I'm getting an error message (without explanation). These are the codes I received from ChatGPT: =INDEX(I:I, MATCH(C3, J:J, 0)) - D3 =IFERROR(INDEX('Sheet2'!$A$2:$A$100, MATCH(C3, 'Sheet2'!$B$2:$B$100, 0)) - D3, "") =IFERROR(INDEX('Inhoud'!$A$2:$A$100, MATCH(C2, 'Inhoud'!$B$2:$B$100, 0)) - D2, "") =IFERROR(VLOOKUP(C2, 'Inhoud'!$B$2:$A$100, 2, FALSE) - D2, "") Thanks in advance for any help!895Views0likes8Comments
Groups
Recent Blog Articles
No content to show