Nested IF Formulas

[Ozbearinvn]

Hi Seems that the Nested IF Formulas is a hard one to work out, well at least for me.
I have a Connector that talks to a SQL Database to get information to build a tariff and am trying to use this to use as a costing tool, I work in Travel.

In General I have gotten it to work with different possible scenarios for the rates and how to treat the totals but I have come across a tricky one.

Most Sightseeing services are Per Person based and so this I have working fine if there is a rate avaialble or to look up a cell for a manually entered rate. But I then found out that there are some services that are based on a group Cost and not PP and so I need to determine firstly if the rate is based on PP or Group and then to either multiply the rate by the number of people on the costing or to leave as a group cost, got this to work fine. The tricky bit is I need to also build into this if it is a Manual rate and to look up the rate.

So any help would be appreciated but unfortunately I can't send a file because of the connector but below is the formulas I am using, if they make any sense.

To get the rate if it is a Per Person or group and treat it accordingly:
=IF(getTariffValue($A5,Parameters!$D$21)="1",getTariffRate($A5,$C5,$H9,,,,$H8)*$H$1,getTariffRate($A5,$C5,$H9,,,,$H8))

When I try to nest another IF statement to try to add in a Manual rate I get a result of FALSE and cannot figure what I have missed or got in the wrong place, the formula is:
=IF(getTariffValue($A5,Parameters!$D$21)=1,getTariffRate($A5,$C5,$H9,,,,$H8)*$H$1,IF(getTariffRate($A5,$C5,$H9,,,,$H8)="N/A",($L9($L9*$H$8/100),getTariffRate($A5,$C5,$H9,,,,$H$8))))

I tried to construct based on another discussion but clearly I am missing something.

I am not sure if anyone can guide me in the right direction but any help or constructive critisism appreciated.

[amy_d2]

Hi

pls post small sample of your data structure in excel, before that remove all sensitive data from it

Thanks
A

[Ozbearinvn]

Hi A,
Sorry for the delay was trying to get something together that would be understandable and have had nothing but problems. Thought I had it figured but then not so much so starting with the basics which is attached.

what I have attached has 3 scenarios and have 2 working but I need to add in the Manual rate bit, so if the Max is = 1 then blah and if not then blah but it is if the Rate is = to 0.00 then the third action and this is the bit I have trouble with.

Anything help appreciated and if you dont understand then let me know and happy to try and explain.

thanks,
Brett

[amy_d2]

if there is no rate its obvious that your value will be 0 ,

or pls let me know what value you want & how, let me know manually calculation

[Ozbearinvn]

Hi Amy,

The rates are obtained from a SQL Database via a connector.

I have 3 scenarios that i need to try and get a result for.

There are 3 types of services, the first is a Group rate that is divided by the number of people on the service for the unit price and then the rate for the total (a markup can be applied to this depending on the quote, the second is a per person rate that needs the rate for the unit price but then multiplied by the number of people to get the total, again a markup needs to be able to be applied.

The third is the one that I have trouble with. Some options in the database are setup as manual, there is not rate loaded as it needs to be obtained per booking. The result with the connector is not actually a zero rate but comes into the document as N/A. I need to be able to build in to the formula to see where it comes as N/A to then look to the Manual rate field and bring that rate in. This rate is generally a per person rate and so the entered rate becomes the unit price and then needs to be multiplied for the and also needs the markup applied.

I was trying to keep it simple with the example I sent as i can't send it with the connector as anyone looking at it would not get any rates.

Let me know if this helps or if more information is needed.

Thanks so much for taking the time to have a look.

Regards,
Brett