search & if functions

Originally Posted by sys-con

I am a complete newbie and have had help in creating a spreadsheet to be used as an invoice template. which is attached.

I am now at a point where i am completely stuck, i will try to explain what i need to do if anyone can shed any light on this it will be much appreciated.

I will use row 12 for ease of explanation, it should become clear if you look at the attached file.

=IF(AND(B12>0,C12>0, NOT(ISBLANK(A12))), IF((B12*C12/10^6)<$C$110,$C$110*LOOKUP(A12,$A$75:$A$89,$C$75:$C$89),
(B12*C12/10^6)*LOOKUP(A12,$A$75:$A$89,$C$75:$C$89)),"")

now i want to include a formulae after the ISBLANK statement i think that looks at column A and if the description includes the words 1st m2 and the area is less than 1m2 then it calculates 1 x rate (a75:a89, c75:c89).

Thanks for taking the time to look.

HI,

my suggestion would be to switch the test around a little for ease of undrstanding/amending.

Currently you have =If( condition, do cost, blank)
and you want to increase the condition and do a different cost.

Easier would be =If( blank condition, "" , do cost)
which can then easily become =If( blank condition, "" ,If( next condition, cost @ $1, do cost))

something like

=IF(OR(ISBLANK(A12),AND(B12=0,C12=0)),"",IF(AND(LEN(A12)>LEN(SUBSTITUTE(A12&"1st mē","1st mē","")),D12<1),"B12*1",IF((B12*C12/10^6)<$C$110,$C$110*LOOKUP(A12,$A$75:$A$89,$C$75:$C$89),(B12*C12/10^6)*LOOKUP(A12,$A$75:$A$89,$C$75:$C$89))))

will show where the new cost should apply, but the rest of the costing escapes me, why multiply by 1 ?

---

Originally Posted by Bryan Hessey

HI,

my suggestion would be to switch the test around a little for ease of undrstanding/amending.

Currently you have =If( condition, do cost, blank)
and you want to increase the condition and do a different cost.

Easier would be =If( blank condition, "" , do cost)
which can then easily become =If( blank condition, "" ,If( next condition, cost @ $1, do cost))

something like

=IF(OR(ISBLANK(A12),AND(B12=0,C12=0)),"",IF(AND(LEN(A12)>LEN(SUBSTITUTE(A12&"1st mē","1st mē","")),D12<1),"B12*1",IF((B12*C12/10^6)<$C$110,$C$110*LOOKUP(A12,$A$75:$A$89,$C$75:$C$89),(B12*C12/10^6)*LOOKUP(A12,$A$75:$A$89,$C$75:$C$89))))

will show where the new cost should apply, but the rest of the costing escapes me, why multiply by 1 ?

---

Thanks for your reply, I have looked at your formulae and when the area is less than 1m2 it returns a text value of 1*b12, i have tried to alter it slightly and it now seems to work.

=IF(OR(ISBLANK(A12),AND(B12=0,C12=0)),"",IF(AND(LEN(A12)>LEN(SUBSTITUTE(A12&"1st mē","1st mē","")),D12<1),1*LOOKUP(A12,$A$75:$A$89,$C$75:$C$89),IF((B12*C12/10^6)<$C$110,$C$110*LOOKUP(A12,$A$75:$A$89,$C$75:$C$89),(B12*C12/10^6)*LOOKUP(A12,$A$75:$A$89,$C$75:$C$89))))

Just to clarify, I want to achieve the following

Cost is calculated by (L(mm) * W (mm) / 10^6(to convert to m2)) * by the rate using lookup to refer to the list of products / prices (A75:A89, C75:C89). However there are a few conditions:

1) if cells A, B & C are blank then do nothing.

2) if (L * W / 10^6) is less than 0.3m2 then charge the minimum area rate (C110 * (L * W / 10^6))

3) if cell A contains the phrase 1st m2 and ( L * W / 10^6) is less than 1m2 charge the minimum 1 * (a75:a89,c75:c89) if the (L * W / 10^6) is greater than 1m2 charge ((L * W /10^6) * (a75:a89,c75:c89))

Thanks

Originally Posted by sys-con

Thanks for your reply, I have looked at your formulae and when the area is less than 1m2 it returns a text value of 1*b12, i have tried to alter it slightly and it now seems to work.

=IF(OR(ISBLANK(A12),AND(B12=0,C12=0)),"",IF(AND(LEN(A12)>LEN(SUBSTITUTE(A12&"1st mē","1st mē","")),D12<1),1*LOOKUP(A12,$A$75:$A$89,$C$75:$C$89),IF((B12*C12/10^6)<$C$110,$C$110*LOOKUP(A12,$A$75:$A$89,$C$75:$C$89),(B12*C12/10^6)*LOOKUP(A12,$A$75:$A$89,$C$75:$C$89))))

Just to clarify, I want to achieve the following

Cost is calculated by (L(mm) * W (mm) / 10^6(to convert to m2)) * by the rate using lookup to refer to the list of products / prices (A75:A89, C75:C89). However there are a few conditions:

1) if cells A, B & C are blank then do nothing.

2) if (L * W / 10^6) is less than 0.3m2 then charge the minimum area rate (C110 * (L * W / 10^6))

3) if cell A contains the phrase 1st m2 and ( L * W / 10^6) is less than 1m2 charge the minimum 1 * (a75:a89,c75:c89) if the (L * W / 10^6) is greater than 1m2 charge ((L * W /10^6) * (a75:a89,c75:c89))

Thanks

Hi,

the formula was deliberatly set to show 1*b12 to demonstrate that the re-arranged formula, with the added test, still performed as before. As I said, I still do not understand the purpose of "charge the minimum 1 * ", but if the formula as amended by you is working for you then our goal has been reached

thanks for the feedback.
---

Originally Posted by Bryan Hessey

Hi,

the formula was deliberatly set to show 1*b12 to demonstrate that the re-arranged formula, with the added test, still performed as before. As I said, I still do not understand the purpose of "charge the minimum 1 * ", but if the formula as amended by you is working for you then our goal has been reached

thanks for the feedback.
---

The idea of charging a minimum of 1 x rate is because the 1st m2 is charged at full rate if less than 1m2 - so for instance if a customer took 3 pieces of glass all 0.5m2 then the 1st piece would be charge at 1 x rate and the other 2 would be charged at 0.5 * rate.

Thanks for you help, much appreciated.

ps. Just realised what you mean 1 x rate could just be replaced with rate, is this what you was getting at.

Originally Posted by sys-con

The idea of charging a minimum of 1 x rate is because the 1st m2 is charged at full rate if less than 1m2 - so for instance if a customer took 3 pieces of glass all 0.5m2 then the 1st piece would be charge at 1 x rate and the other 2 would be charged at 0.5 * rate.

Thanks for you help, much appreciated.

ps. Just realised what you mean 1 x rate could just be replaced with rate, is this what you was getting at.

Hi,

it was, yes, but, if it helps for you to separate 1* from .5 * then the 1 * becomes more understandable. Thanks for the feedback.
---