Nested formula

[9Redwood]

HI All,

I have the below formula, and I have two questions that im having trouble with.

1) Is it possible to shorten this formula? Im quite new and figured this is over kill.
2) I am trying to incorprate an "istext" formula into this as I need cell L3 (where this formula is embedded) to = "Various" if any text is entered into cell H3. Does anyone have ani ideas?

=IF(COUNTIF(C3,"*")=0,"",IF(H3=4,SUM(G3*13),IF(H3=1,SUM(G3*52),IF(H3=2,SUM(G3*26),IF(H3=3,SUM(G3*17.3),IF(H3=8,SUM(G3*6.5),IF(H3=12,SUM(G3*4.3),IF(H3="Printer","Printer",IF(H3=0,G3*4.3)))))))))

Thanks in advance again.

 

[Sal Paradise]

Care to explain your logic?

It looks like you're doing:
G3*52/H3 if the number is 1,2,3,4,8,12
And "Printer" if H3 is printer
And G3*4.3 if H3 is 0 (which looks like 52/12).

What the heck are you trying to do?

 

[mikerickson]

Try
=IF(LEN(C3)=0, "", CHOOSE((N(H3)+1+(H3="Cat")+SIGN(N(H3)), G3*4.3, "Printer", G3*52, G3*26, G3*17.3, G3*13, 5, 6, 7, G3*6.5, 9, 10, 11, G3*4.3))

 

[jbeaucaire]

Maybe this:

=IF(C3="", "", IF(ISTEXT(H3), "Various", G3 * LOOKUP(H3, {0,1,2,3,4,8,12}, {4.3,52,26,17.3,13,6.5,4.3})))

 

[9Redwood]

Sal Paradise, mikerickson, jbeaucaire;

Thank you very much for your feedback.

mikerickson & jbeaucaire - both worked a treat! Thank you for the different approach, I will have to look up what some of those functions are used for.

Sal Paradise, essentially, I'm trying to work out lost service revenue. Its basically multiplying the service cost by the amount of times we service a client per year. Apologies mate, rather new to excel.

 

[Sal Paradise]

Sal Paradise, mikerickson, jbeaucaire;

Thank you very much for your feedback.

mikerickson & jbeaucaire - both worked a treat! Thank you for the different approach, I will have to look up what some of those functions are used for.

Sal Paradise, essentially, I'm trying to work out lost service revenue. Its basically multiplying the service cost by the amount of times we service a client per year. Apologies mate, rather new to excel.

Glad it worked for you. I can't believe they figured out what was going on there!