IF Statements with AND and N/A calculations

Question

I hope you can help. I have a couple of formulas that are breaking due to N/A appearing in cells. I was wondering if these N/A could be accommodated in my formulas to still return a numerical result. In the picture below you can see that in Cell C21 there is 'N/A' and in S21 the is the error #VALUE! The reason for this error is of course because of the 'N/A'

Can the formula in S21 be amended to factor in this 'N/A' and return a number even if it is present. My formula is below

=SUM(AND(C21>=12,C21<=19)*C21,AND(F21>=2,F21<=20)*F21,AND(I21>=2,I21<=20)*I21,AND(L21>=2,L21<=20)*L21)

Here is the picture of the spreadsheet

Answer 1

=IF(ISNA(C21),0,SUM(AND(C21>=12,C21<=19)*C21,AND(F21>=2,F21<=20)*F21,AND(I21>=2,I21<=20)*I21,AND(L21>=2,L21<=20)*L21)) <br>

If cell C21 is N/A cell S21 will 0. You can replace 0 with other number or text.

Answer 2

Untested, but give this a try. You can change the value of zero to whatever you want:

=IFERROR(SUM(AND(C21>=12,C21<=19)*C21,AND(F21>=2,F21<=20)*F21,
AND(I21>=2,I21<=20)*I21,AND(L21>=2,L21<=20)*L21),0)

Answer 3

Try this,

from bs4 import BeautifulSoup
import cloudscraper

url = 'https://gogoplay.io/download?id=MTE3MTk0&title=One+Punch+Man+%28Dub%29&typesub=SUB&sub=&cover=Y292ZXIvb25lLXB1bmNoLW1hbi1kdWIucG5n&refer=https://gogoplay1.com/&ch=d41d8cd98f00b204e9800998ecf8427e'

scraper = cloudscraper.create_scraper(browser = 'chrome')
response = scraper.get(url)
if response.status_code == 200:
    print("downloads page")
    soup = BeautifulSoup(scraper.get(url).text, 'lxml')
    download_low_p_url = soup.find('div', {'class': 'mirror_link'}).find_all('div', {'class': 'dowload'})[0].find('a')['href'] # List contains all the links [360, 480, 780, 1080]. I choose 0 (first, 360p).
    print("getting cdn link for lowest quality", download_low_p_url)
    response = scraper.get(download_low_p_url)
    if response.status_code == 302:
        print("downloading from cdn", response.url)
        response = scraper.get(response.url, headers = {'Referer': 'https://gogoplay1.com/'}) #Referer is necessary else it throws 403
        if response.status_code == 200:
            with open('ep1.mp4', 'wb') as file:
                file.write(response.content)
            print("download complete")
        else:
            print("download error", response.status_code)
    else:
        print("cdn error", response.status_code)
else:
    print("base error", response.status_code)

On checking the requests trail, the download links has a redirecting url with a time bound token included as args. So, I initially sent a request to the downloads page, then to the lowest quality (just to save time on download, server is slow) which have a location in the response header (also known as redirecting url). Finally, we can send the redirect url a request and write the contents to a file.

This is like a skeleton. Add your features (such as option to select quality). Happy learning!

Answer 4

Ran into the same problem, although I was using selenium. Im currently using this workaround.

from selenium import webdriver
from selenium.webdriver import ActionChains
from selenium.webdriver.common.by import By
from selenium.webdriver.firefox.options import Options

url = "https://gogoplay.io/download?id=NzEzNjE=&typesub=Gogoanime-SUB&title=Boku+no+Hero+Academia+Episode+1"
download_dir = "some\\directory" #NOTE: This path MUST use backslashes

opt = Options()#A bunch of options to set the download directory and stop the browser from asking where to download to
opt.set_preference("browser.download.folderList", 2)
opt.set_preference('browser.download.dir', os.path.abspath(download_dir))
opt.set_preference("browser.helperApps.alwaysAsk.force", False)
opt.set_preference("browser.download.manager.showWhenStarting", False)
opt.set_preference("browser.download.manager.showAlertOnComplete", False)
opt.set_preference('browser.helperApps.neverAsk.saveToDisk','video/mp4')
driver = webdriver.Firefox(options=opt)
action = ActionChains(self.driver)

driver.get(url)
dl_button = driver.find_element(By.LINK_TEXT, "DOWNLOAD (1080P - MP4)") # Change to whatever quality you want

action.move_to_element(dl_button).click().perform()
action.move_to_element(dl_button).click().perform() # Because the first one triggers an ad, not the download


import sys
sys.exit(1) # Throw an error to stop the browser from closing before the download completes. 
# Pretty sure there's a cleaner way to stop the browser from closing but this works

The code clicks on the download button instead of trying to get the video directly from the source.

Some caveats though, selenium requires you to download the browser and web driver for that browser. Also, expect the download speed to be much slower than directly downloading from the source. Before when I could hit the source "gogo-cdn" directly, 1 episode took about 20 seconds to download. Now, using the download page, it takes me 10 minutes at best. Sometimes much slower. Not too big of a problem for me but it may be for you.

If you do find a way to get around the 403 issue, please do share though. Thanks!