Oct 08 2020 02:04 PM
I'm trying to create an excel spreadsheet that will calculate price depending on a) number of transactions and b) by Channel Partner. The IF statement is unruly and we are planning on adding more Channel Partners.
How can I make this easier?
Here is an example of the IF statement
=IF($E$21>0,IF($B$4="BMO",IF($B$18="Basic",IF($E$21>='Master Data'!$F$182,'Master Data'!$I$182,IF($E$21>='Master Data'!$F$181,'Master Data'!$I$181,IF($E$21>='Master Data'!$F$180,'Master Data'!$I$180,'Master Data'!$I$179))),IF($B$18="Complete",IF($E$21>='Master Data'!$F$189,'Master Data'!$I$189,IF($E$21>='Master Data'!$F$188,'Master Data'!$I$188,IF($E$21>='Master Data'!$F$187,'Master Data'!$I$187,IF($E$21>='Master Data'!$F$186,'Master Data'!$I$186,IF($E$21>='Master Data'!$F$185,'Master Data'!$I$185,IF($E$21>='Master Data'!$F$184,'Master Data'!$I$184,'Master Data'!$I$183)))))),0))))
An example of the for cell J21 in spread below
Oct 08 2020 02:48 PM - edited Oct 08 2020 02:51 PM
Life would become far easier if you could set your Master Data up so that it is searchable rather than hard-wiring the links between sheets manually. For example
= LOOKUP( 1,
1 /(Packages[CP Code]=Partner)
/(ISNUMBER(SEARCH(Level, Packages[CP Packages]))),
Packages[AW Price] )
You will also need to include a test on 'Setup', 'Maintenance' etc.
Oct 09 2020 05:47 AM
@Peter Bartholomew Thank you for your reply.. are you saying my Master Data is not setup efficiently? Most of my cells are dependent on 2 - 3 criteria. It would be much appreciated if you could provide the LOOKUP formula for cell G18 & G21 so I may use it as an example. Your help is much appreciated. Thank you.
Oct 09 2020 11:38 AM
What I had in mind was that entries like
MO MAINTENANCE PACK C; includes B2B
are designed to be informative to the reader but it is not so easy to determine whether it is an exact match for a criterion value from the overview sheet. For searching you need fields which contain specific key words such as SETUP or MAINTENANCE which allow for searches that return an exact match. Additional information that does not form part of the search criteria should be restricted to comment fields that are not referenced by formulas.
I would normally use XLOOKUP to search for data that matches a combination of criteria but that requires Excel 365, so I have used LET in your workbook.
Oct 09 2020 02:35 PM
I'd do not rely on direct lookups since texts to lookup could vary (e.g. Package (C) = PACK C, etc.). Perhaps parallelly to data validation lists
create one more range which maps above on position of cell to pick-up. Or even better to link here cells from which values ae to be taken, with that we are less dependent from insertion/deleting rows in main list.
But it very depends on how information about the packages is updated - is it fixed structure or not, does text in CP Packages are exactly the same all the time or they could vary (Package(C) = PACK C =
PACKAGE C = pkg C = etc).
Other words, it's not enough knowledge of business logic on our site (which could be obvious for you).
Oct 09 2020 02:47 PM
I think we are in agreement.
The nested IFs look a nightmare to me; I think I might even prefer to copy/paste the values across from the second sheet than try to validate the formulas!
Oct 09 2020 03:41 PM
It's possible to survive with complex nested IF generating it first time. Perhaps even second time to modify the logic in initial formula. But on third time you start thinking that maybe it'll be better to change the job.
Oct 12 2020 05:49 AM
@Sergei Baklan I have most of the formulas working nicely... thanks to your ideas. However I have not figured out how to identify a price based on the number of transactions (see highlighted in yellow). Sorry, I couldn't follow your formula. Could you provide another hint?
Oct 12 2020 08:33 AM
If you clean your data in general you don't need helper column in Master Data, in place of H18
it could be
=INDEX('Master Data'!$G$19:$G$90,MATCH($B$4&$B$18,'Master Data'!$C$19:$C$90&'Master Data'!$D$19:$D$90,0))
or
=XLOOKUP($B$4&$B$18,'Master Data'!$C$19:$C$90&'Master Data'!$D$19:$D$90,'Master Data'!$G$19:$G$90,"NA")
For the next block
I'd add helper column in Master Data to indicate combination, like
with it formulas could be
=LOOKUP($E21,'Master Data'!$F$100:$F$104,'Master Data'!$H$100:$H$104)
and
=LOOKUP($E21,1/($B$4='Master Data'!$C$94:$C$160)/('Biller Overview'!$B$18='Master Data'!$J$94:$J$160)*'Master Data'!$F$94:$F$160,'Master Data'!$I$94:$I$160)
Oct 13 2020 01:25 PM
@Sergei Baklan Hi Sergei, thanks for you tips... worked great! I'm still trying to get the pricing correct in the yellow highlighted areas. In cells I21 - I24, these prices are based on the Channel Partner. Additionally it can be based on the Package, but not always. For example, BMO has different pricing for Basic vs Complete where MUFG just has one pricing. Any ideas? BTW, I'm working on removing the Helper field (B16 - B90) because I don't think its necessary with your formulas, but wanted to see if you had something for the price. ....
Oct 14 2020 01:47 PM
We may expand criteria for that column as
('Master Data'!$J$94:$J$160 = B18) OR ('Master Data'!$J$94:$J$160 is empty)
Formula
=LOOKUP(
$E21,
1/($B$4='Master Data'!$C$94:$C$160)/
(
('Biller Overview'!$B$18='Master Data'!$J$94:$J$160)+
(""='Master Data'!$J$94:$J$160)
)*
'Master Data'!$F$94:$F$160,
'Master Data'!$I$94:$I$160
)
is in Q21 in attached.
Oct 19 2020 07:26 AM
Thank you for your help on this project...
I feel I am getting to be a better excel developer.
I only have a few wrap up issues that I was wonder if you had any suggestions...
Is there a better way of creating the list values rather than the way I have them on the Master Data row 2 - 9 / columns H - T?
I39 = Number of CSR Licenses
Search by CP, Package, CSR, number of licenses
H41 = For some reason it can't file Stored at Biller for Aliaswire
i41 = can't find Stored at Aliaswire for MUFG
G58 = I'm not sure what to do there yet
How do I set the Channel Partner field so I can send it to a Channel Partner without them seeing the other Channel Partner data?
How do I protect the Biller Overview page so only the input fields are available?
How do I hide / protect the Master Data so people cant change or see it?
Any suggestions would greatly be appreciated...
Oct 21 2020 10:59 AM
Oct 21 2020 12:48 PM
For that you shall to protect cells on which check-boxes have references, actually Master Data sheet.
Oct 22 2020 11:49 AM
@Sergei Baklan I tried that.. but still spaced out the text without leave a check. Also, I added a Channel Partner and the =INDIRECT(B4) in cell B22 stopped working.. why is that?
Oct 22 2020 01:01 PM
INDIRECT() doesn't work only for Citi, but this name isn't defined
If protect Master Data sheet (or part of it to which check boxes are linked) user can't change check box status, it'll be the message as
Oct 22 2020 01:12 PM - edited Oct 22 2020 01:24 PM
@Sergei Baklan Where are the indirect values defined? I can't find Citi CCB anywhere in the spreadsheet. How do you look at those indirect values? I'll be adding new Channel Partners like Citi... how can this easily be done? Thank you for your help!
Oct 22 2020 03:02 PM
You may define/edit it in Name Manager
but more easy way is to select the range and enter the name into Name box as it is done, for example, for MUFG
Oct 23 2020 11:24 AM
@Sergei Baklan so how am I suppose to "protect" check boxes so the User can tab to the field and use either the mouse or space bar to insert a check? Thanks!
Oct 23 2020 02:06 PM
Sorry, I don't know if that's possible. I'd suggest to ask as separate question (open new conversation).