• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

addition to a specific formula to deduct 1 pax each certain Number

Hany ali

Active Member
hello my masters, i want your help to amend for this formula

Code:

=IF(OR($B3="",$C3=""),"",$C3*VLOOKUP($C$1&$F3,Prices!$B$3:$F$714,4,0)+$D3*VLOOKUP($C$1&$F3,Prices!$B$3:$F$714,5,0))

to can to deduct 1 pax each number of pax in Cell J1 Value
in the same items each Invoice
I mean for Example ,if I have in this invoice like more than 10 pax in
compact car … it should be deduct 1 pax each 10 pax( cell J1)
 

Attachments

  • 1.png
    1.png
    49.2 KB · Views: 4
  • Deduct.xlsm
    59.9 KB · Views: 5
Last edited by a moderator:
Maybe,

In E3, copied down :

=IF(($B3="")+($C3=""),"",SUM(VLOOKUP($C$1&$F3,Prices!$B$3:$F$714,{4,5},0)*($C3:$D3-INT($C3:$D3/$J$1))))

66544

Regards
Bosco
 
thanks alot my Dear... it's very Nice Array
but when i have for example in the same Invoice from the same item 33 pax ,it should to deduct 3 pax ,even in differant Rows in the Invoice
 

Attachments

  • 1.png
    1.png
    54.9 KB · Views: 8
  • Deduct.xlsm
    62.6 KB · Views: 5
Last edited:
This additional requirement would require longer formula

In E3, copied down :

=IF(($B3="")+($C3=""),"",VLOOKUP($C$1&$F3,Prices!$B$3:$F$714,4,0)*($C3-INT(SUMIF($F$2:$F3,$F3,$C$2:$C3)/$J$1)+INT(SUMIF($F$2:$F2,$F3,$C$2:$C2)/$J$1))+VLOOKUP($C$1&$F3,Prices!$B$3:$F$714,5,0)*($D3-INT(SUMIF($F$2:$F3,$F3,$D$2:$D3)/$J$1)+INT(SUMIF($F$2:$F2,$F3,$D$2:$D2)/$J$1)))

66568
 
Back
Top