HELP WRITE AN EXCEL CALCULATION FORMULA!

Start by defining a name.

lpif(left(sheet1!$a$2:$a$27,8)=left(evaluate("a" & row())8),sheet1!$b$2:$b$27,"")

Then c2=(int((sum(1*(lp="a"))-1)/3)+1)*25+(int((sum(1*(lp="b"))-1)/3)+1)*27+(int((sum(1*(lp="c"))-1)/3)+1)*30+(int((sum(1*(lp="d"))-1)/3)+1)*35+(int((sum(1*(lp="e"))-1)/3)+1)*38

Array formulas.

I don't really understand, so I sent the problem as an attachment to the content and sample table (please note: if you want to be in the 03 version, the current situation and the target effect will be expressed).

The description is not very clear.,Write a pseudo**.,Write it yourself if you understand it.,Or don't understand it, send an email to haohan.,I can help you write.,But you have to charge a little tip.。

for(i=1;i《Number of Lines; i++)

if(substr(cell(i,1),1,9)==""If (i!=1)

var2=the number of dynamic arrays.

if(var2%3==0)

number of sets=(int)(var2 3);

else:number=(int)(var2 3)+1;

if(dynamic array[0]=='a')

**=Unit price*Number of sets;

Print the result in the previous cell.

current tracking number=substr(cell(i,1),1,8);

counter=0 'Dynamic array indexes are zeroed.

dynamic array[0]=cell(i,2);

else if(substr(cell(i,1),1,8)==current tracking number).

var1=cell(i,2)

If var1 is not in a dynamic array.

Place var1 in a dynamic array.

The extrapolation problem for the "use time(d)" item in sheet 1 should be the "number of days" from "replace time" to "today". If you design the formula along these lines, in Excel 2007, the formula "replace time" under "50 lamp (day)" in the "record table" (titled sheet 1) is available=text(vlookup(a3,'50 lamp history'!$a$2:

b$3,2,false),"yyyy/mm/dd")"Meet the requirements of the question;

"Use Time(d)" under "50 lamp(day)" in the record sheet (titled "sheet 1)" under "use time(d)" "available formula".=if(b3-today()<0,(b3-today())1),b3-today())"Meet the requirements of the question;

Note 1:To extract the data in the "75 lamp history" and "12k lamp history" table in the "Record Table", you can refer to the method of extracting the data of "50 lamp history", and in the corresponding cell, you only need to change "50 lamp history" to the corresponding ** name.

Note 2:In the formula for "use time(d)", "(b3-today()))1)" is used instead of "today()-b3", because "today()-b3" results in date format, while "b3-today()" will have a negative number.

Draft the question to ** as shown in the figure below:

Draft the question to the ** diagram.

The result of the above formula is shown in the figure below:

Illustration of the results of the above formula.

(mod((a1+b1),24)>=0)*(mod((a1+b1),24)<=6)*6+(mod((a1+b1),24)>=13)*(mod((a1+b1),24)<=18)*19+(mod((a1+b1),24)>=20)*(mod((a1+b1),24)<=23)*6

With this formula, it's pretty straightforward!

Analysis: Between 0-6:

mod((a1+b1),24)>=0)*(mod((a1+b1),24)<=6)*6

If it is not between 0 and 6, 0 is returned

Between 13 and 18: (mod((a1+b1),24)>=13)*(mod((a1+b1),24)<=18)*19

If it is not between 13-18, 0 is returned

Between 20 and 24: (mod((a1+b1),24)>=20)*(mod((a1+b1),24)<=23)*6

If it is not between 20-24, 0 is returned

One of the three numbers produced by the above three formulas must be 6 or 19, the other two are equal to 0, and the sum of the three numbers is still 6 or 9

So, you can do this without ifs.

It's clearer to replace mod((a1+b1),24) with c1:

The formula is as follows: (c1>=0)*(c1<=6)*6+(c1>=13)*(c1<=18)*19+(c1>=20)*(c1<=23)*6

If you don't understand, please ask!

Note: The form between 0-6 here includes the two numbers of 0 and 6, if it is not included, it can be removed from the = sign in "= and <=, but I don't understand what the number should be returned if 7-12 and 9 appear in C1! If you use the above formula, the result will be 0.

Step 1. 1)if(and(c1>=0,c1<=6),6,?If c1 is between 0 and 6, d1 is 6 otherwise. Be?

What is it? Step 2.

2)if(and(c1>=13,c<=18),19,?Step 3. 3)if(and(c1>=20,c<=23),6,"No longer in scope")

Merge. 3) Equation in 2) Equation question mark.

Then put it to the question mark of the 1) formula. And the results came out.

if(and(c1>=0,c1<=6),6,if(and(c1>=13,c<=18),19,if(and(c1>=20,c<=23),6,"No longer in scope"))

Comrade, don't always ask how to write a formula for you, but be able to design a formula yourself.

if(and(c1>=0,c1<=6),6,if(and(c1>=13,c1<=18),19,if(and(c1>=20,c1<=23),6,"No decision has been made"I don't know if the [between 0-6] you said includes 0 and 6, if not, just remove the equal sign in the formula.

What should d1 be when there is a missing question, between c1=7-12?

Personally, I think it's more convenient to use Vlookup.

d1=vlookup(c1,,2,true)